Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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 "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
I added ""'s
=CELL("address", INDEX("I6:I333", MATCH("B16, I6":I333, 0) ) ) Thanks again "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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 "baconcow" wrote: I added ""'s =CELL("address", INDEX("I6:I333", MATCH("B16, I6":I333, 0) ) ) Thanks again "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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. baconcow wrote: 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 "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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 wrote: 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 "baconcow" wrote: I added ""'s =CELL("address", INDEX("I6:I333", MATCH("B16, I6":I333, 0) ) ) Thanks again "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
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" wrote: 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. baconcow wrote: 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 "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Origin Cell from Data Validation
You may like application.match() even more!!!
baconcow wrote: 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" wrote: 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. baconcow wrote: 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 "shg" wrote: Or =CELL("address", INDEX(I6:I333, MATCH(B16, I6:I333, 0) ) ) -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24666 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing data validation list in IF Statement | Excel Programming | |||
Data Validation Restriction Referencing other Workbook | Excel Worksheet Functions | |||
Referencing a range on a different tab than where data validation | Excel Discussion (Misc queries) | |||
Referencing a data validation list | Excel Discussion (Misc queries) | |||
Data validation referencing other cells' values | Excel Programming |