Compare Range A & B where C = "Y"

[rabingupta]

Dear All,


I have this small macro code written but there are couple of queries which I have got:

1. It is very slow
2. The value given by Vlookup is not same as the macro code being executed. Please help!!


My Code:
Dim ForIndex As Integer
Dim ForIndex1 As Integer
Dim Counter As Integer


Sub Generate_Missing_Items()
Counter = 0
With Sheet1
Sh1LastRow = .Cells(Rows.Count, "F").End(xlUp).Row
Set Sh1Range = .Range("F1:F" & Sh1LastRow)
End With

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

Application.ScreenUpdating = False
For ForIndex = 2 To Sh1LastRow
For ForIndex1 = 2 To Sh2LastRow

If Sheet1.Range("F" & ForIndex).Value <> Empty And Sheet1.Range("F" & ForIndex).Value = Sheet1.Range("H" & ForIndex1).Value And UCase(Sheet1.Range("H" & ForIndex1).Value) = "Y" Then
Counter = Counter + 1
Sheet2.Range("A" & ForIndex).Value = Sheet1.Range("F" & Counter).Value
End If

Next
Next
Application.ScreenUpdating = True
Sheet1.Range("A" & 8).Value = Counter

End Sub


Rabin

 

[strongm]

Might I suggest the eminently more suitable forum707

This forum is for VB rather than VBA