Refering to a particular cell in a Named Range

[Corey ....]

I have a named range in sheet2 called "Locations"

In sheet 1 i use a validation dropdown list on required cells to list the
Location values.
I am trying to identify IF the value chosen was a particular item from the
"Locations" list.

"Locations" = Sheet2 A1-A12

I selected the first value in the list in the Validation List in Sheet1
(which is actally Sheet2 A1)

How can i capture the correct cell value chosen?
If Range("C5").Value = Locations(Cells(1)) Then ' <=== ???
'do stuff here
end if


Corey....

 

[Bob Phillips]

If Not IsError(Application.Match(Range("C5").Value, Range("Locations"),0))
Then

MsgBox Application.Match)Range("C5").Value, Range("Locations"),0) ' the
index within Locations
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I'm not 100% sure what you are actually looking for, so here is a shot-gun
type of an answer hoping that one of these is what you want.

So, if you are just looking to see if the value in C5 is in the list
somewhere, you could do this...

If Not Range("Locations").Find(Range("C5").Value) Is Nothing Then
' C5 was in the list, so do your stuff here
End If

Now, although I left them out for this example, it would be a good idea to
include the references to the worksheet for each of the ranges (which is
necessary to do if they reside on different worksheets). If, instead, you
want to know if it matches a particular item in the list, I would think you
could test for that directly (using the contents of the cell you are
interested in match as opposed to a functional reference to the content).
Assuming you are looking specific match a particular index value, you can
get that index value within the list using this...

MatchedIndexValue = Range("Locations").Find(Range("C5").Value).Row -
Range("Locations").Row + 1

And, again, it would be a good idea to reference the worksheet for each of
the ranges. If, instead of the index location within the list itself, you
just want the row the match occurred on, then you would use this...

RowMatchWasOn = Range("Locations").Find(Range("C5").Value).Row

One last thing to note... since your list started on Row 1, the values in
MatchedIndexValue and RowMatchWasOn are identical (this will only happen
when the list starts on Row 1).

Rick

 

[cush]

Change:

If Range("C5").Value = Locations(Cells(1)) Then ' <=== ???
'do stuff here
end if


TO:

If Range("C5").Value = Range("Locations")(1) Then
'do stuff here
end if

This will check to see if Sheet1.Range("C5") matches the first item in your
Locations list. If it matches, your 'do stuff' will execute.