Finding and pasting over a row
 

Hi, hope someone might be able to help me.....

I have data in rows on sheet 1 where column A is the unique referenc
and am pulling a single row of that data into sheet2 via VLOOKUP an
altering some of it.
I then want to copy the entire row in sheet2, and find the location o
the original row in sheet1 based on the unique reference and paste th
new values over the top of that row.

Make sense? It's obviously part of a much larger project, but I'
kinda stuck at this stage. Your help would be greatly appriciated.

Regards,

Chris

--
Message posted from http://www.ExcelForum.com

Finding and pasting over a row
 

Dim rng1 as Range, rng2 as Range
Dim cell1 as Range, cell2 as Range
Dim res as Variant
' set source range
With worksheets("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
' set range on sheet2
With worksheets("Sheet2")
set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

for each cell2 in rng2
res = application.Match(cell2.value,rng1,0)
if not iserror(res) then
set cell1 = rng1(res)
cell2.entireRow.Copy
cell1.Pastespecial xlValues
end if
Next

--
Regards,
Tom Ogilvy

"Big Chris " wrote in message
...
Hi, hope someone might be able to help me.....

I have data in rows on sheet 1 where column A is the unique reference
and am pulling a single row of that data into sheet2 via VLOOKUP and
altering some of it.
I then want to copy the entire row in sheet2, and find the location of
the original row in sheet1 based on the unique reference and paste the
new values over the top of that row.

Make sense? It's obviously part of a much larger project, but I'm
kinda stuck at this stage. Your help would be greatly appriciated.

Regards,

Chris.


---
Message posted from http://www.ExcelForum.com/