The Find Method

[abxy]

ok, a userform macro that i've created takes waaay too long to complet
it's task of searching thru the entire workbook to find whatever cel
has the same value that's in TextBox1. now, in an effort to try an
speed up the macro, i'm trying to limit what it's searching thru...i
still needs to search thru the entire workbook for the cell that ha
the same value as textbox1, but the cell is always going be in column
between A1 and A275.

here's the single line of the code as is(ws was defined as WorkShee
earlier in the code):

ws.Cells.Find(what:=textbox1.value).Activate

...now, my guess is that i can change the line to this:

ws.Cells.Range("A1:A275").Find(what:=textbox1.value).Activate

or this:

ws.Cells.Find(what:=textbox1.value).Range("A1:A275").Activate

...and one of those guesses will change it so that it only searche
thru A1 to A275...however I'm not sure if either works, if one of the
works, can you verify to that, and tell me, and if they don't, can yo
offer any help on how i can put that range on

 

[Bernie Deitrick]

Hello No-Name,

You need to make sure you have a match prior to activating the cell:

Dim myCell As Range

Set myCell = ws.Range("A1:A275").Find(what:=textbox1.value)
If Not myCell Is Nothing Then
myCell.Activate
End If

HTH,
Bernie
MS Excel MVP

 

[abxy]

i have safe guards in the macro (i hope, lol) so that the cell has t
have a match before it can be activated, but are you saying that

ws.Range("A1:A275").Find(what:=textbox1.value)

would be the correct way to place the range so that the find metho
only searches thru A1 to A275

 

[Bernie Deitrick]

Yes. That is the correct syntax to limit your search to A1:A275

HTH,
Bernie
MS Excel MVP

 

[abxy]

i'll emplement it now and see if my macro runs any noticably faster
thanks mate ! :