I need help writing a macro that will copy data from E2.Sht1 to E2.Sht2 if
A2.Sht1 = A2.Sht2, but it needs to be able to search all cells in column
A.Sht1 for a match to the data in A2.Sht2, for each row in Sht2. both lists
are extensive (ie: over 3000 cells in a column). I need this for work. I'm
using Excel 2003. I also need to be able to understand what i'm writing so
that I can explain it to those that come after me.

Sub ABC()
Dim sh1 as Worksheet, rng1 as Range, cell1 as Range
Dim sh2 as Worksheet, rng2 as Range, cell2 as Range
Dim res as Variant
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
' get range of cells with data in column A of sheet1
set rng1 = sh1.Range(sh1.Range("A2"),sh1.Range("A2").End(xldo wn))
' get range of cells with data in column A of sheet2
set rng2 = sh2.Range(sh2.Range("A2"),sh2.Range("A2").End(xldo wn))
' now loop through the cells in column A of sheet2 and check for
' matches in column A of sheet1
for each cell2 in rng2
res = application.Match(cell2,rng1,0)
' if a match is found then . . .
if not iserror(res) then
' set a reference to the matching cell
set cell1 = rng1(res)
' now copy the cell in column E of that row
' back to column E for the "cell2" we are looking at
sh1.cellsZ(cell1.row,"E").copy sh2.cells(cell2.row,"E")
end if
Next cell2
End Sub

--
Regards,
Tom Ogilvy




"Marie" wrote:

I need help writing a macro that will copy data from E2.Sht1 to E2.Sht2 if
A2.Sht1 = A2.Sht2, but it needs to be able to search all cells in column
A.Sht1 for a match to the data in A2.Sht2, for each row in Sht2. both lists
are extensive (ie: over 3000 cells in a column). I need this for work. I'm
using Excel 2003. I also need to be able to understand what i'm writing so
that I can explain it to those that come after me.

Tried to run and it gave me a compile error: Method or data member not found
for
.cellsZ
in third to last line of code.

How do I correct?

Thanks

"Tom Ogilvy" wrote:

Sub ABC()
Dim sh1 as Worksheet, rng1 as Range, cell1 as Range
Dim sh2 as Worksheet, rng2 as Range, cell2 as Range
Dim res as Variant
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
' get range of cells with data in column A of sheet1
set rng1 = sh1.Range(sh1.Range("A2"),sh1.Range("A2").End(xldo wn))
' get range of cells with data in column A of sheet2
set rng2 = sh2.Range(sh2.Range("A2"),sh2.Range("A2").End(xldo wn))
' now loop through the cells in column A of sheet2 and check for
' matches in column A of sheet1
for each cell2 in rng2
res = application.Match(cell2,rng1,0)
' if a match is found then . . .
if not iserror(res) then
' set a reference to the matching cell
set cell1 = rng1(res)
' now copy the cell in column E of that row
' back to column E for the "cell2" we are looking at
sh1.cellsZ(cell1.row,"E").copy sh2.cells(cell2.row,"E")
end if
Next cell2
End Sub

--
Regards,
Tom Ogilvy




"Marie" wrote:

I need help writing a macro that will copy data from E2.Sht1 to E2.Sht2 if
A2.Sht1 = A2.Sht2, but it needs to be able to search all cells in column
A.Sht1 for a match to the data in A2.Sht2, for each row in Sht2. both lists
are extensive (ie: over 3000 cells in a column). I need this for work. I'm
using Excel 2003. I also need to be able to understand what i'm writing so
that I can explain it to those that come after me.

the Z looks like a stray character to me. I would remove it.

--
Regards,
Tom Ogilvy


"Marie" wrote in message
...
Tried to run and it gave me a compile error: Method or data member not
found
for
.cellsZ
in third to last line of code.

How do I correct?

Thanks

"Tom Ogilvy" wrote:

Sub ABC()
Dim sh1 as Worksheet, rng1 as Range, cell1 as Range
Dim sh2 as Worksheet, rng2 as Range, cell2 as Range
Dim res as Variant
set sh1 = Worksheets("Sheet1")
set sh2 = Worksheets("Sheet2")
' get range of cells with data in column A of sheet1
set rng1 = sh1.Range(sh1.Range("A2"),sh1.Range("A2").End(xldo wn))
' get range of cells with data in column A of sheet2
set rng2 = sh2.Range(sh2.Range("A2"),sh2.Range("A2").End(xldo wn))
' now loop through the cells in column A of sheet2 and check for
' matches in column A of sheet1
for each cell2 in rng2
res = application.Match(cell2,rng1,0)
' if a match is found then . . .
if not iserror(res) then
' set a reference to the matching cell
set cell1 = rng1(res)
' now copy the cell in column E of that row
' back to column E for the "cell2" we are looking at
sh1.cellsZ(cell1.row,"E").copy sh2.cells(cell2.row,"E")
end if
Next cell2
End Sub

--
Regards,
Tom Ogilvy




"Marie" wrote:

I need help writing a macro that will copy data from E2.Sht1 to E2.Sht2
if
A2.Sht1 = A2.Sht2, but it needs to be able to search all cells in
column
A.Sht1 for a match to the data in A2.Sht2, for each row in Sht2. both
lists
are extensive (ie: over 3000 cells in a column). I need this for work.
I'm
using Excel 2003. I also need to be able to understand what i'm
writing so
that I can explain it to those that come after me.