Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranges spanning worksheets | Excel Worksheet Functions | |||
how to merge ranges from multiple worksheets | Excel Worksheet Functions | |||
Copying ranges of data between worksheets | Excel Worksheet Functions | |||
Worksheets, Ranges & Unknowns | Excel Programming | |||
Comparing Ranges in two worksheets | Excel Programming |