Search range and replace data

[redspanna]

Hi all,

In sheet1 cell A2 I have a ref number eg 1234567
I would like a macro to search through cells A12:A100 of sheet2 for the same ref number and when found replace the data in that row (columns A:O) with the data held in columns A:O of sheet1
any ideas please?
many thanks

 

[Makrini]

*Untested so I may make typos*

Dim Cell as variant

For Each Cell in Sheets("Sheet2").Range("A12:A100")
    If Cell.Value = Sheets("Sheet1").Range("A2").Value Then
           Cell.EntireRow.Value = Sheets("Sheet1").Range("A2").EntireRow.Value
    End if
Next

I'm sure there is a neater way without the loop..

 

[erik.van.geit]

Hi,

To my sense you can
1. autofilter column A for your reference number
2. copy
3. paste in filtered sheet: entire range at once (without loop)
even manually, it would take less than 1 minute

If that works for you, you can record your operations (using macro recorder) and you will have useful code. If you want help to edit the code, then display it here.

kind regards,
Erik

 

[redspanna]

Hi Makrini and thanks for the code..

Dim Cell as variant

For Each Cell in Sheets("Sheet2").Range("A12:A100")
    If Cell.Value = Sheets("Sheet1").Range("A2").Value Then
           Cell.EntireRow.Value = Sheets("Sheet1").Range("A2").EntireRow.Value
    End if
Next

..it almost works, but just places a value of '1' in the correct row in sheet2 instead of the whole row of data from sheet1 A:O

and ideas?

 

[erik.van.geit]

not sure if you saw my reply

 

[redspanna]

Hi erik,

Yep, thanks just got it all to work as I wanted, so I guess its a big thanks to you and makrini for all you efforts.,

Once again thanks !