Help needed with Vlookup & copy row to new sheet
 

Hi, i have an application where i have two sheets with data on, variable
in number of rows. I need to loop down one in column "A",(Sheet
19_10_06) and check if the number exists in column "A" on the second
sheet (Oct_06), if it is continue to next number. If it is not on the
second sheet then copy entire row and past in new sheet and continue
until end of data in column "A".
The end result being a comparison of numbers in columns "A" of both
sheets and the differences pasted in a new sheet (New gAMS).

Any help would be greatly appreciated.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help needed with Vlookup & copy row to new sheet
 

Sub ABC()
Dim sh1 as worksheet, sh2 as worksheet, sh3 as worksheet
Dim rng1 as Range, rng2 as Range
Dim rw as Long, cell as Range
set sh1 = worksheets("19_10_06")
set sh2 = worksheets("Oct_06")
set sh3 = worksheets("gAMS")
rw = 2
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown ))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown ))
for each cell in rng1
if application.countif(rng2,cell.value) = 0 then
cell.EntireRow.copy sh3.cells(rw,1)
rw = rw + 1
end if
Next

End sub

--
Regards,
Tom Ogilvy

"Les Stout" wrote:

Hi, i have an application where i have two sheets with data on, variable
in number of rows. I need to loop down one in column "A",(Sheet
19_10_06) and check if the number exists in column "A" on the second
sheet (Oct_06), if it is continue to next number. If it is not on the
second sheet then copy entire row and past in new sheet and continue
until end of data in column "A".
The end result being a comparison of numbers in columns "A" of both
sheets and the differences pasted in a new sheet (New gAMS).

Any help would be greatly appreciated.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help needed with Vlookup & copy row to new sheet
 

Thanks for your reply & help Tom, i am on my way back to South Africa
today so will try it on Monday.

best regards,

Les Stout

Tom Ogilvy wrote:

Sub ABC()
Dim sh1 as worksheet, sh2 as worksheet, sh3 as worksheet
Dim rng1 as Range, rng2 as Range
Dim rw as Long, cell as Range
set sh1 = worksheets("19_10_06")
set sh2 = worksheets("Oct_06")
set sh3 = worksheets("gAMS")
rw = 2
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown ))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown ))
for each cell in rng1
if application.countif(rng2,cell.value) = 0 then
cell.EntireRow.copy sh3.cells(rw,1)
rw = rw + 1
end if
Next

End sub

--
Regards,
Tom Ogilvy

"Les Stout" wrote:

Hi, i have an application where i have two sheets with data on, variable
in number of rows. I need to loop down one in column "A",(Sheet
19_10_06) and check if the number exists in column "A" on the second
sheet (Oct_06), if it is continue to next number. If it is not on the
second sheet then copy entire row and past in new sheet and continue
until end of data in column "A".
The end result being a comparison of numbers in columns "A" of both
sheets and the differences pasted in a new sheet (New gAMS).

Any help would be greatly appreciated.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***