Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 ranges
I'm attempting to look at every value in a discontinuguous range (defined
name of "billraterange") and see if it matches any of the values in another range (defined name of "raterange"). If so, tell me the cell address in "billraterange". I'm open to other approaches beside the use of MATCH. Thanks for your help! Jason ************** Private Sub Worksheet_Deactivate() Dim ValRng As Range Dim Answer As Variant Set RateRng = Sheets("Input").Range("raterange") For Each ValRng In ThisWorkbook.Sheets("Input").Range("billraterange" ) Answer = Application.WorksheetFunction.Match(ValRng.Value, RateRang, 0) If IsError(Answer) Then MsgBox ValRng.Address End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 ranges
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 ranges
Dim ValRng As Range
Dim Answer As Variant Set RateRng = Sheets("Input").Range("raterange") For Each ValRng In Sheets("Input").Range("billraterange") Set varFound = RateRng.Find(ValRng.Value) If Not varFound Is Nothing Then MsgBox varFound.Address End If Next If this post helps click Yes --------------- Jacob Skaria "Jason Morin" wrote: I'm attempting to look at every value in a discontinuguous range (defined name of "billraterange") and see if it matches any of the values in another range (defined name of "raterange"). If so, tell me the cell address in "billraterange". I'm open to other approaches beside the use of MATCH. Thanks for your help! Jason ************** Private Sub Worksheet_Deactivate() Dim ValRng As Range Dim Answer As Variant Set RateRng = Sheets("Input").Range("raterange") For Each ValRng In ThisWorkbook.Sheets("Input").Range("billraterange" ) Answer = Application.WorksheetFunction.Match(ValRng.Value, RateRang, 0) If IsError(Answer) Then MsgBox ValRng.Address End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 ranges
Jacob,
Your code got me going in the right direction. Thanks and thanks to Don, too. Jason "Jacob Skaria" wrote: Dim ValRng As Range Dim Answer As Variant Set RateRng = Sheets("Input").Range("raterange") For Each ValRng In Sheets("Input").Range("billraterange") Set varFound = RateRng.Find(ValRng.Value) If Not varFound Is Nothing Then MsgBox varFound.Address End If Next If this post helps click Yes --------------- Jacob Skaria "Jason Morin" wrote: I'm attempting to look at every value in a discontinuguous range (defined name of "billraterange") and see if it matches any of the values in another range (defined name of "raterange"). If so, tell me the cell address in "billraterange". I'm open to other approaches beside the use of MATCH. Thanks for your help! Jason ************** Private Sub Worksheet_Deactivate() Dim ValRng As Range Dim Answer As Variant Set RateRng = Sheets("Input").Range("raterange") For Each ValRng In ThisWorkbook.Sheets("Input").Range("billraterange" ) Answer = Application.WorksheetFunction.Match(ValRng.Value, RateRang, 0) If IsError(Answer) Then MsgBox ValRng.Address End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare two or more ranges | Excel Worksheet Functions | |||
Compare ranges | Excel Worksheet Functions | |||
Using IF to compare ranges | Excel Worksheet Functions | |||
Compare Ranges | Excel Programming | |||
Compare ranges | Excel Programming |