Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
2003, 2007
37 | CheckStr = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1" (BTW, the string above does not make any sense in its current form - just to test) Data Table produced a previous Sub() above string ConstOnly: 9 StartPosInStr: 2 StrLength: 1 ConstOnly: 7 StartPosInStr: 25 StrLength: 1 ConstOnly: 28038.66 StartPosInStr: 37 StrLength: 8 ConstOnly: 35 StartPosInStr: 46 StrLength: 2 ConstOnly: 1 StartPosInStr: 72 StrLength: 1 myArr() table is as follows: myArr(1,1) = 9 myArr(1,2) = 2 myArr(1,3) = 1 myArr(2,1) = 7 myArr(2,2) = 25 myArr(2,3) = 1 myArr(3,1) = 28038.66 myArr(3,2) = 37 myArr(3,3) = 8 myArr(4,1) = 35 myArr(4,2) = 46 myArr(4,3) = 2 myArr(5,1) = 1 myArr(5,2) = 72 myArr(5,3) = 1 What is the quickest/smartest Array Matching/Finding/Search? To ascertain: Givens: TestChar = "2" Its position in CheckStr = 37 IF (TestChar's "2" ; StartPosInStr = 37) = myArr(x,2) Then TestCharValid = True Else TestCharValid = False End if In short, is it best to do an Array loop Then: IF (TestChar's "2" ; StartPosInStr = 37) = myArr(3,2) Then TestCharValid = True Else TestCharValid = False End if Or Is there an array search function which can directly VLookup? all Array(2,x)?? TIA EagleOne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
For a completely generic text string (like you posted), any search method
will find the "2" in "F26" before it will find the "2" in 28038.66^35. In order to be able to differentiate between the two "2"'s, there would have to be some kind of "positional pattern" to the characters in the text string. Perhaps if you gave us real example text strings to look at, instead of such a random example one, then perhaps we will be able to discern (or ask you for clarification) an underlying pattern upon which search code can be built. -- Rick (MVP - Excel) wrote in message ... 2003, 2007 37 | CheckStr = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1" (BTW, the string above does not make any sense in its current form - just to test) Data Table produced a previous Sub() above string ConstOnly: 9 StartPosInStr: 2 StrLength: 1 ConstOnly: 7 StartPosInStr: 25 StrLength: 1 ConstOnly: 28038.66 StartPosInStr: 37 StrLength: 8 ConstOnly: 35 StartPosInStr: 46 StrLength: 2 ConstOnly: 1 StartPosInStr: 72 StrLength: 1 myArr() table is as follows: myArr(1,1) = 9 myArr(1,2) = 2 myArr(1,3) = 1 myArr(2,1) = 7 myArr(2,2) = 25 myArr(2,3) = 1 myArr(3,1) = 28038.66 myArr(3,2) = 37 myArr(3,3) = 8 myArr(4,1) = 35 myArr(4,2) = 46 myArr(4,3) = 2 myArr(5,1) = 1 myArr(5,2) = 72 myArr(5,3) = 1 What is the quickest/smartest Array Matching/Finding/Search? To ascertain: Givens: TestChar = "2" Its position in CheckStr = 37 IF (TestChar's "2" ; StartPosInStr = 37) = myArr(x,2) Then TestCharValid = True Else TestCharValid = False End if In short, is it best to do an Array loop Then: IF (TestChar's "2" ; StartPosInStr = 37) = myArr(3,2) Then TestCharValid = True Else TestCharValid = False End if Or Is there an array search function which can directly VLookup? all Array(2,x)?? TIA EagleOne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
Thanks Rick.
My question is: If I already know that the "2" (I want) is in Position 37 of CheckStr, but I want to confirm that it is found/exists in myArr(x,2); (As per my data table i.e.: 2,25,37,46,72) How do I do that? "Rick Rothstein" wrote: For a completely generic text string (like you posted), any search method will find the "2" in "F26" before it will find the "2" in 28038.66^35. In order to be able to differentiate between the two "2"'s, there would have to be some kind of "positional pattern" to the characters in the text string. Perhaps if you gave us real example text strings to look at, instead of such a random example one, then perhaps we will be able to discern (or ask you for clarification) an underlying pattern upon which search code can be built. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
Rick, let me restate as I now understand your confusion.
What I should have stated: I need to know if "2" Position of 37 is found within the string-ranges of StartPosInStr: 2 + StrLength: 1 or Positions 2-3 StartPosInStr: 25 + StrLength: 1 or Positions 25-26 StartPosInStr: 37 + StrLength: 8 or Positions 37-45 ....etc. Sorry for my confusion! "Rick Rothstein" wrote: For a completely generic text string (like you posted), any search method will find the "2" in "F26" before it will find the "2" in 28038.66^35. In order to be able to differentiate between the two "2"'s, there would have to be some kind of "positional pattern" to the characters in the text string. Perhaps if you gave us real example text strings to look at, instead of such a random example one, then perhaps we will be able to discern (or ask you for clarification) an underlying pattern upon which search code can be built. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
I don't think I am completely following how you have things setup, but
perhaps the following will help you out. You can find out what character is located at any specified position in a text string by using the Mid function... Dim Character As String Character = Mid(YourTextString, StartPosition, 1) The 1 in the third argument is because we are looking for a single character, but the number there is the number of characters to retrieve starting from the StartPosition specified in the second argument. So, if you wanted the single character appearing at position 25 in the example text string you posted, then this would give it to you... Character = Mid(CheckStr, 25, 1) On the other hand, if you wanted the 8 characters starting from position 25 of the string (that would be the 28038.66), you would do this... Characters = Mid(CheckStr, 25, 8) -- Rick (MVP - Excel) wrote in message ... Thanks Rick. My question is: If I already know that the "2" (I want) is in Position 37 of CheckStr, but I want to confirm that it is found/exists in myArr(x,2); (As per my data table i.e.: 2,25,37,46,72) How do I do that? "Rick Rothstein" wrote: For a completely generic text string (like you posted), any search method will find the "2" in "F26" before it will find the "2" in 28038.66^35. In order to be able to differentiate between the two "2"'s, there would have to be some kind of "positional pattern" to the characters in the text string. Perhaps if you gave us real example text strings to look at, instead of such a random example one, then perhaps we will be able to discern (or ask you for clarification) an underlying pattern upon which search code can be built. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
Actually:
StartPosInStr: 2 + StrLength: 1 or Positions 2-2 StartPosInStr: 25 + StrLength: 1 or Positions 25-25 StartPosInStr: 37 + StrLength: 8 or Positions 37-44 ....etc. Sorry for my confusion! "Rick Rothstein" wrote: For a completely generic text string (like you posted), any search method will find the "2" in "F26" before it will find the "2" in 28038.66^35. In order to be able to differentiate between the two "2"'s, there would have to be some kind of "positional pattern" to the characters in the text string. Perhaps if you gave us real example text strings to look at, instead of such a random example one, then perhaps we will be able to discern (or ask you for clarification) an underlying pattern upon which search code can be built. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
Please see my other posts.
I think the last one previous to this may be helpful. For my specific VBA situation, I need to know that "2" in Pos 37 of CheckStr is also in the array "myArr(x,2)". I need the VBA code to ascertain if Pos 37 is within the Start Positions + StrLength in myArr EagleOne "Rick Rothstein" wrote: I don't think I am completely following how you have things setup, but perhaps the following will help you out. You can find out what character is located at any specified position in a text string by using the Mid function... Dim Character As String Character = Mid(YourTextString, StartPosition, 1) The 1 in the third argument is because we are looking for a single character, but the number there is the number of characters to retrieve starting from the StartPosition specified in the second argument. So, if you wanted the single character appearing at position 25 in the example text string you posted, then this would give it to you... Character = Mid(CheckStr, 25, 1) On the other hand, if you wanted the 8 characters starting from position 25 of the string (that would be the 28038.66), you would do this... Characters = Mid(CheckStr, 25, 8) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
I think my confusion comes from my not understanding exactly what is in your
myArr array. Can you explain in more detail, and perhaps with a specific example, what elements make up your array for the given example text string? -- Rick (MVP - Excel) wrote in message ... Please see my other posts. I think the last one previous to this may be helpful. For my specific VBA situation, I need to know that "2" in Pos 37 of CheckStr is also in the array "myArr(x,2)". I need the VBA code to ascertain if Pos 37 is within the Start Positions + StrLength in myArr EagleOne "Rick Rothstein" wrote: I don't think I am completely following how you have things setup, but perhaps the following will help you out. You can find out what character is located at any specified position in a text string by using the Mid function... Dim Character As String Character = Mid(YourTextString, StartPosition, 1) The 1 in the third argument is because we are looking for a single character, but the number there is the number of characters to retrieve starting from the StartPosition specified in the second argument. So, if you wanted the single character appearing at position 25 in the example text string you posted, then this would give it to you... Character = Mid(CheckStr, 25, 1) On the other hand, if you wanted the 8 characters starting from position 25 of the string (that would be the 28038.66), you would do this... Characters = Mid(CheckStr, 25, 8) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching/Finding/Search Array Element
The contents of myArr is in my first post in this thread and below.
For sure, my challenge has been a moving target as my own thoughts evolve. I now realize that multiple calculations must be made therefore a loop must be used. myArr Table of data: myArr(1,1) = 9 myArr(1,2) = 2 myArr(1,3) = 1 myArr(2,1) = 7 myArr(2,2) = 25 myArr(2,3) = 1 myArr(3,1) = 28038.66 myArr(3,2) = 37 myArr(3,3) = 8 myArr(4,1) = 35 myArr(4,2) = 46 myArr(4,3) = 2 myArr(5,1) = 1 myArr(5,2) = 72 myArr(5,3) = 1 Sub Test() .... .... CheckStr = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1" TestStrChar = "2" TestStrPos = 37 TestCharValid = False ..... ..... ..... For For N = 1 To UBound(myArr) N = N +1 If myArr(N,2) = TestStrPos and TestStrPos <= (myArr(N,2) + myArr(N,3)) -1 Then TestCharValid = True Exit For End if Loop End Sub MEMO info: StartPosInStr: 2 + StrLength: 1 or Positions 2-2 StartPosInStr: 25 + StrLength: 1 or Positions 25-25 StartPosInStr: 37 + StrLength: 8 or Positions 37-44 Is it possible that I have answered my own question? Is there a better way or is my guess incorrect? Rick, thanks for your time and knowledge! EagleOne "Rick Rothstein" wrote: I think my confusion comes from my not understanding exactly what is in your myArr array. Can you explain in more detail, and perhaps with a specific example, what elements make up your array for the given example text string? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rules for element-by-element product in array multiplication | Excel Programming | |||
Search array and return element No | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Finding the cell reference of a matching search value | Excel Worksheet Functions |