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

TD Rich

Active Member
Joined
Aug 10, 2010
Messages
343
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:eek:
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
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.


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top