Copy and Paste in different locations depending on adjacent cell value

I have the below which is meant to look for the first instance of "Test" in column B and once found copies the cells directly to the left of it and pastes it onto Sheet3 Cell AJ3 if the value of the cell 2 cells to the right of where 'Test' is found matches cell C1 but it should paste it to SHeet3 AK3 if the cell 2 cells to the right of where 'Test' is found matches cell D1. The code runs but does nothing. I'm really close I think so it must just need some minor tweaks?

Please Login or Register  to view this content.

This line looks unusual :

Please Login or Register  to view this content.

as does

Please Login or Register  to view this content.

Your Excel must be different to mine if you have a row 0
Which row are you trying to select?

Originally Posted by Kevin#

This line looks unusual :

Please Login or Register  to view this content.

as does

Please Login or Register  to view this content.

Your Excel must be different to mine if you have a row 0
Which row are you trying to select?

So if it finds "Test" in say cell B90 then rng(0, 2) would be cell D90. Is that right?

Originally Posted by xladept

Maybe:

Please Login or Register  to view this content.

That runs without error much like my previous code, but it does nothing. It's like it doesn't meet the criteria to paste, but it definitely does. Is it something to do with the cell references and offsets I'm thinking?

Sample.xlsm

Attached Sample file

Sample.xlsm

Silly me, attached the wrong one. Correct on here.

Originally Posted by xladept

I put "Test" in B32 and ran this:

Please Login or Register  to view this content.

It runs without error, but it doesn't paste anything....it should match what is in 'Sheet 3 After' in the sample sheet.

I see what has happened. OK, the latest attached sheet and the Sheet3 After is what I want after the macro runs. You'll see that it'll need some sort of loop I'm guessing?

Sample.xlsm

Think we're close now.

Final alteration made to the sample sheet now if you could review. Once this is done I'll give you even more rep!

Final-Sample.xlsm

Originally Posted by xladept

Hi ScabbyDog,

Try this:

Please Login or Register  to view this content.

The recent code you posted works perfectly on my sample sheet provided but not when I transfer the code to the sheet I am working on. I've attached the exact data I'll be generally using to see if you can get it to work with it! No idea why it wouldn't be working as the cell references are still correct. I'm guessing it's something to do with finding the last used row in column B?

Final-Sample.xlsm

Thanks so much! Really appreciate your help with this. Solved!