Searching a limited amount of rows in a vertical array

[Joe Miller]

How do I search thru vertical array A1:A100 a variable number of rows where the number of rows is defined by a constant in cell B1? For example if I want to scan the first fifty rows cell B1 will contain 50.

[Don Guillett]

homework?

--
Don Guillett
SalesAid Software
[email protected]
"Joe Miller" <[email protected]> wrote
in message news:[email protected]...
>
> How do I search thru vertical array A1:A100 a variable number of rows
> where the number of rows is defined by a constant in cell B1? For
> example if I want to scan the first fifty rows cell B1 will contain 50.
>
>
> --
> 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
>

[Joe Miller]

Hi Don Guillett,
I don’t understand ‘homework”?
Joe Miller

[JLatham]

He's asking is this a homework assignment for a class.

Also, is this solution to be a formula in a worksheet cell or is it to be VB
code? Or will either do? What is being sought in the range A1:A100? What
is supposed to be done/happen when you find what you're looking for?

If in VB, use the contents of cell B1 as the terminating value for a loop,
as (assumes sheet with data on it is Sheet1:

Worksheets("Sheet1").Select
Range("A1").select
For LoopCounter = 1 to Worksheets("Sheet1").Range("B1").Value
....move thru the cells and do testing inside the loop
Next

faster method would be to use LoopCounter as a row offset, which would need
a setup like this:
Worksheets("Sheet1").Select
Range("A1").select
For LoopCounter = 0 to Worksheets("Sheet1").Range("B1").Value-1
....move thru the cells via .Offset and do testing inside the loop
Next

To be robust we'd want to add verification that the contents of B1 is valid
(positive integer from 1 to 100) before beginning the loop.

"Joe Miller" wrote:

>
> Hi Don Guillett,
> I don’t understand ‘homework”?
> Joe Miller
>
>
> --
> 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
>
>

[Joe Miller]

Many thanks JLatham.
I had no idea VB could be used in Excel. I have written VB programs so maybe I will be able to stumble thru and make use of it. You have opened up for me what sounds powerful and potentially very useful for a lot of the stuff I am trying to do.
I submitted my Forum question mainly to verify that what I am trying to do is do-able in Excel before trying to do it, and it sounds like it probably can be done. I would like to do it in just Excel. I will work on it for a while and maybe submit another question to the Forum.
Thanks again.

[Don Guillett]

a bit quicker I would think
Sub findb()
x = Range("i1:i" & Range("b1")).Find("b").Row
MsgBox x
End Sub

or a formula
=VLOOKUP("b",INDIRECT("I1:I"&B1),1,0)
--
Don Guillett
SalesAid Software
[email protected]
"JLatham" <[email protected]> wrote in message
news:[email protected]...
> He's asking is this a homework assignment for a class.
>
> Also, is this solution to be a formula in a worksheet cell or is it to be
> VB
> code? Or will either do? What is being sought in the range A1:A100?
> What
> is supposed to be done/happen when you find what you're looking for?
>
> If in VB, use the contents of cell B1 as the terminating value for a loop,
> as (assumes sheet with data on it is Sheet1:
>
> Worksheets("Sheet1").Select
> Range("A1").select
> For LoopCounter = 1 to Worksheets("Sheet1").Range("B1").Value
> ...move thru the cells and do testing inside the loop
> Next
>
> faster method would be to use LoopCounter as a row offset, which would
> need
> a setup like this:
> Worksheets("Sheet1").Select
> Range("A1").select
> For LoopCounter = 0 to Worksheets("Sheet1").Range("B1").Value-1
> ...move thru the cells via .Offset and do testing inside the loop
> Next
>
> To be robust we'd want to add verification that the contents of B1 is
> valid
> (positive integer from 1 to 100) before beginning the loop.
>
> "Joe Miller" wrote:
>
>>
>> Hi Don Guillett,
>> I don't understand 'homework"?
>> Joe Miller
>>
>>
>> --
>> 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
>>
>>

[JLatham]

Actually I kind of figured it was homework and that you were into doing loops
which is why I wrote what I did. For a real world solution that would be
faster even than looping using .Offset(), look at Don Guillet's response to
my reply above. The Find will do it much quicker.

"Joe Miller" wrote:

>
> Many thanks JLatham.
> I had no idea VB could be used in Excel. I have written VB programs so
> maybe I will be able to stumble thru and make use of it. You have
> opened up for me what sounds powerful and potentially very useful for a
> lot of the stuff I am trying to do.
> I submitted my Forum question mainly to verify that what I am trying to
> do is do-able in Excel before trying to do it, and it sounds like it
> probably can be done. I would like to do it in just Excel. I will work
> on it for a while and maybe submit another question to the Forum.
> Thanks again.
>
>
> --
> 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
>
>

[Joe Miller]

What is it about my question that looks suspiciously like homework? Both of you with the same impression makes me wonder what it is (ie) what is so different from the thousands of other questions on the Forum. I have no problem with it – just wondering. Anyhow thanks to both of you.
Joe Miller

[JLatham]

Trying for the second time ... I didn't actually think of it in that way, I
was just responding to your question back to Don Guillett. Although I can
understand why he would have thought of the possibility. Most people
describe the job at hand, the 'why' of it all so to speak. You didn't. Your
very succinct requirements kind of look like a synopsis of a well defined
requirement. You should consider that a compliment - quite often it takes a
while just to interpret the question. A regular poster here commented, in
jest, recently that he usually ignored the original question and just read
the responses and then tries to divine the original question. Sometimes I
think that's not a bad way to attack many of them.

Again, glad to have been a small part of what I hope turns out to be an
effective solution, thanks mostly go to Don Guillet who provided a very
concise, efficient way of attacking the problem.

"Joe Miller" wrote:

>
> What is it about my question that looks suspiciously like homework? Both
> of you with the same impression makes me wonder what it is (ie) what is
> so different from the thousands of other questions on the Forum. I have
> no problem with it – just wondering. Anyhow thanks to both of you.
> Joe Miller
>
>
> --
> 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
>
>

[Joe Miller]

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 guidance 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

[Don Guillett]

copy paste to a module and execute

--
Don Guillett
SalesAid Software
[email protected]
"Joe Miller" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[JLatham]

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
>
>

[Joe Miller]

Dear JLatham,

Your in-depth reply is exactly what I needed and much more than I expected. Thank you - you have saved me a lot of time and effort.

Joe Miller