writing a macro for search-match-copy
 

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.

writing a macro for search-match-copy
 

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.

writing a macro for search-match-copy
 

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.

writing a macro for search-match-copy
 

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.