Matching/Finding/Search Array Element

[EagleOne]

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() Re: 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

 

[Rick Rothstein]

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.

 

[EagleOne]

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?

 

[EagleOne]

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]

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)

 

[EagleOne]

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!

 

[EagleOne]

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]

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?

 

[EagleOne]

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