worksheets and ranges
 

I need to compare values in two different worksheets within the same
workbook. So I want logic like

for each c in range1

for each d in range 2

if c....value = d....value then

do something
end if

next d

next c

The problem is that I can create the first range by using an active sheet.
When I try to do a second range using worksheets(index) I get an error. What
am I doing wrong? How do I do it correctly?

worksheets and ranges
 

with worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with

with worksheets("Sheet2")
set rng2 = .Range(.Cells(1,3),.Cells(rows.count,3).End(xlup))
End with

for each cell in rng
if application.Countif(rng2,cell) 0 then
msgbox "Match found for " & cell & " in " & rng2.Address(external:=true)
end if
next

if you need to know the location
for each cell in rng
if application.Countif(rng2,cell) 0 then
res = Application.Match(cell,rng2,0)
msgbox "Match found for " & cell & " in " &
rng2(res).Address(external:=true)
end if
next


naturally msgbox is illustrative and you would apply your own functionality.
--
Regards,
Tom Ogilvy

"David Gerstman" wrote:

I need to compare values in two different worksheets within the same
workbook. So I want logic like

for each c in range1

for each d in range 2

if c....value = d....value then

do something
end if

next d

next c

The problem is that I can create the first range by using an active sheet.
When I try to do a second range using worksheets(index) I get an error. What
am I doing wrong? How do I do it correctly?

worksheets and ranges
 

Thank you so much. This has helped me tremendously. I'm sorry I got back too
late and couldn't rate the response.
David

"Tom Ogilvy" wrote:

with worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with

with worksheets("Sheet2")
set rng2 = .Range(.Cells(1,3),.Cells(rows.count,3).End(xlup))
End with

for each cell in rng
if application.Countif(rng2,cell) 0 then
msgbox "Match found for " & cell & " in " & rng2.Address(external:=true)
end if
next

if you need to know the location
for each cell in rng
if application.Countif(rng2,cell) 0 then
res = Application.Match(cell,rng2,0)
msgbox "Match found for " & cell & " in " &
rng2(res).Address(external:=true)
end if
next


naturally msgbox is illustrative and you would apply your own functionality.
--
Regards,
Tom Ogilvy

"David Gerstman" wrote:

I need to compare values in two different worksheets within the same
workbook. So I want logic like

for each c in range1

for each d in range 2

if c....value = d....value then

do something
end if

next d

next c

The problem is that I can create the first range by using an active sheet.
When I try to do a second range using worksheets(index) I get an error. What
am I doing wrong? How do I do it correctly?