What he has done is find the row number that the first item found is in into
variable named 'x'. The MsgBox simply displays that result to you. The
variable x is what you'd work with beyond this point to do something. More
on that later.

Remember that your cell B1 has the last row number that you want to search
to. What the statement that Don provided does is to a trick to use that to
create an address (or range) look at
("i1:i" & Range("b1"))
the ampersand (&) joins together two text strings, so the result of that
operation is to create a range that might (presuming you had 51 in B1) look
like this i1:i51 - same as writing Range("i1:i51") - except that you get to
change that on the fly based on the content of B1. The rest of the statement
is kind of self explanatory, it says 'find the character "b" within the range
I just gave you (i1:i51) and tell me what row you found it on. You could
have asked for other things to be returned besides .Row, such as .Column
(which would always be 9, since column I is column 9) or .Value which would
always return the same thing you're looking for, since that's what it found.

Now, that leaves us asking what can we now do with x? You could do some
math with it, or lots of things in code, or you could simply go to that cell
with a line of code again using that concatenation trick like this:
Range("i" & x).Select
although you could have short circuited that back at the place where we got
x with a line such as
Range("i1:i" & range("b1")).Find("b").Select
perhaps you'd like to choose another cell on the same row where the match
was found, you could do something like
Range("a" & x).Select
so you looked in column I, found a match and point out related cell in
column A.

Ok, now you know WHAT is going to happen, and now you need to know how to
make that happen. Easiest way without further ado is to simply choose
Tools | Macro |Macros and highlight the findb entry and click the [Run]
button.
But that's inconvenient, especially to someone that doesn't know that such a
macro exists.

Another way is to create a kind of pretty button to click and make it
happen. You can take a shape from the Drawing toolbar (View | Toolbars |
Drawing) and drop it on the sheet and then dress it up with color, nice
border, some text, and then right-click it and choose Assign Macro and point
it to the macro findb. Now when you click on that drawing object the code
will run. Another, similar way is to place a control from the forms toolbar
directly onto the worksheet and assign the macro to its _Click event.

If you really wanted to get kind of fancy, you could have the code run each
time that the value in B1 was changed. That would be done with code similar
to this attached to the worksheet's _Change event:

Sub Worksheet_Change(Target as Range)
Dim iSect as Range

Set iSect = Application.Intersect(Range(Target.Address),Range( "B1"))
If iSect Is Nothing then
Exit Sub ' do nothing because change wasn't in B1
End If
' here we go with the code
x = Range("i1:i" & Range("B1")).Find("b").Row
MsgBox x
End Sub

Now more than likely in a case like this you're not so much going to have to
vary the range examined, but probably want to find different things. So lets
say that not only do you have the number of rows to search in B1, but you
have what to search for in C1, you could modify the code slightly like this:
x = Range("i1:i" & Range("B1")).Find(Range("C1")).Row

More properly (i.e., more formally) these would be written as
x = Range("i1:i" & Range("B1").Value).Find(Range("C1").Value).Row
but .Value is the default property, so it's usually left out for brevity.

Hope this all helps some. Remember that while inside of the VB Editor, you
can select a keyword and hit [F1] and usually get a whole lot of help about
it. When you've chosen a keyword that relates to an object, such as a Range,
you'll also be able to see a list of Methods (things you can do with it,
actions related to it) and Properties (things that change it's appearance or
the way it interacts with the user or application).

For a little help on how to get into the three main areas of the VB Editor,
general purpose, Worksheet event modules and/or Workbook event modules, start
on this page:
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
it tells about general purpose code access, and has links to pages telling
how to get to the modules for worksheets/workbooks.


"Joe Miller" wrote:


I am copying/creating the VB example in my Excel User's Guide - trying
to become familiar with the required concepts necessary to implement VB
into my Excel programs. In the meantime I will appreciate any suggestion
about where to find additional quidance telling me how to implement the
following 4 lines of VB code which was suggested by Don Guillett in a
previous entry in this thread.
------- 4 lines of VB code
Sub findb()
x = Range("i1:i" & Range("b1")).Find("b").Row
MsgBox x
End Sub


--
Joe Miller
------------------------------------------------------------------------
Joe Miller's Profile: http://www.excelforum.com/member.php...o&userid=29900
View this thread: http://www.excelforum.com/showthread...hreadid=573460