worksheets and ranges

[David Gerstman]

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?

[Tom Ogilvy]

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?
>

[David Gerstman]

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?
> >