Referencing a Origin Cell from Data Validation

[baconcow]

I have a data validation list set up at "B16". The list goes from "I6" to
"I333".

If I select an item from the drop-down list, for example, "UNITABC", it will
appear in "B16". The value was taken from "I300", though.

How do I, with VBA, refer that cell? I want to be able to output what cell
("I300") that is currently selected in the data validation drop-down list.

Thanks
Shawn

 

[Dave Peterson]

You're just looking for the address?

=if(b16="","","I"&match(b16,i6:i333,0)+row(i6)-1)
=if(b16="","","I"&match(b16,i6:i333,0)+5)

=match() will return the row (in the range) of the matching cell.

So if the match was in I6, =match() would return a 1 (first row of the range).
So we add 5 since we're starting in I6.

 

[baconcow]

Thanks to you both. I eventually used this bit of code that I made. However,
it is a lot less optimized.

' Locate Ship To in data validation list
For cnt = 0 To 327
shipto = Range("B16").Value
If shipto = shipto_rng.Offset(cnt) Then
Set cur_rng = shipto_rng.Offset(cnt)
End If
Next cnt

 

[baconcow]

I added ""'s

=CELL("address", INDEX("I6:I333", MATCH("B16, I6":I333, 0) ) )

Thanks again

 

[baconcow]

I am actually having an issue with this now:

=CELL("address", INDEX("I6:I333", MATCH("B16", "I6:I333", "0") ) )

It doesn't like the Match function and says "Sub or function not defined".
Without the "", it gives me a variable error. Also, where does "address" come
from? Thanks

 

[Dave Peterson]

You don't need to change shipto inside the loop. And once you find a match (and
do the Set), then you may as well leave the loop:

Set Cur_rng = nothing
shipto = Range("B16").Value
For cnt = 0 To 327
If shipto = shipto_rng.Offset(cnt) Then
Set cur_rng = shipto_rng.Offset(cnt)
Exit for
End If
Next cnt

if cur_rng is nothing then
'not found
else
'found at cur_rng.address
end if

=========
But I didn't realize that you were using code.

I'd use:

Dim ShipTo as variant 'string???
dim res as variant 'could be an error
dim myRng as range
dim cur_rng as range

set myrng = activesheet.range("I6:i333")
shipto = activesheet.range("b16").value

res = application.match(shipto, myrng, 0)

if iserror(res) then
msgbox "not found"
else
set cur_rng = myrng(res)
msgbox cur_rng.address
end if

It may be a bit faster than looping.

 

[Dave Peterson]

These are worksheet functions--not functions built into VBA. But they can be
used, but in this case, you don't need to use all of them.

Check another branch in this thread.

 

[baconcow]

Damn, you've always got an answer to all of my issues. I did not know you
could exit a for loop. I like that. Thanks again.

 

[Dave Peterson]

You may like application.match() even more!!!

Damn, you've always got an answer to all of my issues. I did not know you
could exit a for loop. I like that. Thanks again.