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

You can use FIND

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Jason Morin" <(E-Mail Removed)> wrote in message
news:4BCF69E6-CF17-4216-80F3-(E-Mail Removed)...
> 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

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

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