Code is driving me MAD!!!!!!! ;-)

[TD Rich]

Hello,

I have the following piece of code that should look for a value stated in the last cell in column A (sheet 3) then use this value to find corresponding cells in sheet 2 and then return the cells for cloumns A-F for that look up. At the mome3nt it is only returning the first example it finds rather than all. Can anyone help please?

here is the code:

Sheets("sheet2").Activate
For k = 3000 To 1 Step -1
If Cells(k, "H").Value = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Value Then
Range("A" & k).Resize(, 8).Copy
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next k

Regards,

TD Rich

 

[xenou]

Hi,

1) Does it make any difference if we fully qualify both sheets? (code example below)
2) What kind of values are these? Sometimes the matching itself isn't precise due to issues like text vs. numeric or the storage of floats.


Note: we can also drop the searched for value into a variable to save having to find this each loop.

Dim x
x = Worksheets("Sheet3").Cells(Rows.Count,1).End(xlUp).Value
With Sheets("sheet2")
    For k = 3000 To 1 Step -1
    If .Cells(k, 8).Value = x Then
        .Cells(k, 1).Resize(1, 8).Copy
        Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial _
		    Paste:=xlPasteValues
    End If
Next k