Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
Hi Don Guillett, I dont 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 dont 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 "JLatham" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 literature telling me how to implement the following 4 lines of VB code which was suggested by Don Guillett in a previous entry in this question and answer string. ------- 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
copy paste to a module and execute
-- Don Guillett SalesAid Software "Joe Miller" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a limited amount of rows in a vertical array
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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple rows to other worksheets (but amount of rows varies) - How? | Excel Discussion (Misc queries) | |||
complicated array | Excel Worksheet Functions | |||
Finding minimum value across selected rows of an array | Excel Worksheet Functions | |||
Finding min,max in an array using selected rows from a table | Excel Worksheet Functions | |||
Adding rows to an array | Excel Worksheet Functions |