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