Code Optimization Request

[rabingupta]

Hi Guys,

I would highly appreciate if you can help me to optimize my code.

My Problem:
Compare Col A (Total Rows = 35000) and Col B (1500) get all the values matching in Row A, where Col C = "Y". I have this code but its taking too long to run, Please help me optimize the code.

My Code:

Dim ForIndex As Integer
Dim ForIndex1 As Integer
Dim ForIndex3 As Integer
Dim ForIndex4 As Integer
Dim Counter As Integer


Sub Generate_Missing_Items()

Counter = 0
Application.ScreenUpdating = False

With Sheet3
Sh1LastRow = .Cells(Rows.Count, "F").End(xlUp).Row
Set Sh1Range = .Range("F1:F" & Sh1LastRow)
End With

With Sheet3
Sh2LastRow = .Cells(Rows.Count, "H").End(xlUp).Row
Set Sh2Range = .Range("H1:H" & Sh2LastRow)
End With

With Sheet3
Sh3LastRow = .Cells(Rows.Count, "I").End(xlUp).Row
Set Sh1Range = .Range("F1:F" & Sh3LastRow)
End With

For ForIndex1 = 2 To Sh2LastRow
For ForIndex = 2 To Sh1LastRow
If Sheet3.Range("H" & ForIndex1).Value = Sheet3.Range("F" & ForIndex).Value And UCase(Sheet3.Range("G" & ForIndex).Value) = "Y" Then
Counter = Counter + 1
Sheet5.Range("A" & Counter).Value = Sheet3.Range("F" & ForIndex).Value
Sheet5.Range("B" & Counter).Value = Sheet3.Range("G" & ForIndex).Value
End If
Next
Next

For ForIndex3 = 2 To Sh3LastRow
For ForIndex4 = 2 To Sh1LastRow
If Sheet3.Range("I" & ForIndex3).Value = Sheet3.Range("F" & ForIndex4).Value And UCase(Sheet3.Range("G" & ForIndex4).Value) = "Y" Then
Counter2 = Counter2 + 1
Sheet5.Range("C" & Counter2).Value = Sheet3.Range("F" & ForIndex).Value
Sheet5.Range("D" & Counter2).Value = Sheet3.Range("G" & ForIndex).Value
End If
Next
Next


Application.ScreenUpdating = True

Sheet3.Range("A" & 8).Value = Counter

End Sub


Thanks,
Rabin

 

[combo]

Without coding: use MS Query in another workbook (data>import external data>new database query), do not use wizard.
BTW, dim index as long, it is outside integer scope.

combo

 

[rabingupta]

Hi Combo,

I am sorry I am required to use code as there are further things associated with this task.

Is it possible to convert the for loop in a code based vlookup??

 

[SkipVought]

"I am sorry I am required to use code as there are further things associated with this task."

Can you be more specific regarding the "further things?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

In pure VBA you could:
[li]assign Sh1Range, Sh2Range and Sh3Range to variants (vSh1Range=Sh1Range), you will get 2D variant arrays[/li]
[li]consider additional array that contains only those rows where col. G ="Y", esp. useful if there is significantly smaller than Sh1LastRow,[/li]
[li]generate output array in memory (2D, even if it has one column), assign it to range at the end of job.[/li]

combo