Help requested to finish Array code

[EagleOne]

2003, 2007


Wish to capture multi-position/values of " ~ " in MainS (via Range("A1".Value = Position(i).Value )

MainS = "~972552~2~~3" (which is the OrigStr below, with all operators replaced with "~"

OrigStr = "(972552/2)*3"



Therefore the Position(i) array manually would be:

Option Base 1
Dim Position(4)

Position(1) "("
Position(2) "/"
Position(3) ")"
Position(4) "*"

Requesting help to finish the code below to pragmatically handle the Array.

Sub OperatorPositionsValueInStr()

Dim MainS As String
Dim SubS As String
Dim i As Long
Dim k As Long
OrigStr = "(972552/2)*3"
'MainS = "~972552~2~~3" (memo only)
SubS = "~"

'Obtain number of "~" in MainS
For i = 1 To Len(MainS)
If Mid(MainS, i, Len(SubS)) = SubS Then
k = k + 1
End If
Next i

Dim Positions(k)
For i = 1 To k
'How do I code so that each Position(i) in MainS is saved
' separately in the Array Position(i)?
'
' "????" is an increasing number to the full Len(MainS)
'
Positions(i) = InStr("????", MainS, "~", vbTextCompare)
Next i

'Output example:
Range("A" & i).Value = Position(i).Value

End Sub

*******************************
My brain goes into complete fart-mode when it comes to Arrays.
Especially multiple demensional which may seem to be appropriate
in this case i.e. Position(Position in MainS, Value in MainS)

Any thoughts greatly appreciated

EagleOne

 

[Dave Peterson]

I'm not quite sure what the question is, but maybe this will help:

Option Explicit
Sub OperatorPositionsValueInStr()

Dim OrigStr As String
Dim MainS As String
Dim SubS As String
Dim iCtr As Long
Dim HowManyTildes As Long
Dim StartingPos As Long
Dim Positions() As Long
Dim myChars As String

myChars = "()/*"
OrigStr = "(972552/2)*3"
SubS = "~"

MainS = OrigStr
For iCtr = 1 To Len(myChars)
MainS = Replace(MainS, Mid(myChars, iCtr, 1), SubS)
Next iCtr

HowManyTildes = Len(MainS) - Len(Replace(MainS, SubS, ""))

If HowManyTildes = 0 Then
MsgBox "no ~'s found!"
Else
ReDim Positions(1 To HowManyTildes)
StartingPos = 1
For iCtr = 1 To HowManyTildes
Positions(iCtr) = InStr(StartingPos, MainS, SubS, vbTextCompare)
StartingPos = Positions(iCtr) + 1
Next iCtr
ActiveSheet.Range("a1").Resize(HowManyTildes, 1).Value _
= Application.Transpose(Positions)
End If

End Sub

VBA's Replace was added in xl2k. If you're using xl97, you can use
application.substitute().

 

[EagleOne]

Hello Dave,

Bottom line, I wish to initialize a 2D Array Posiion(1 = PosInStr, 2 = CharacterItSelf)

myChars = "()/*"
OrigStr = "(972552/2)*3"

Therefore your proposed strings: (Did I get the 1,1; 2,2, etc. correct below?)
Operator(1,1) would be (1,"(")
Operator(2,2) would be (8,"/")
Operator(3,3) would be (10,")")
Operator(4,4) would be (11,"*")

Instr() will give me the position, but I was not sure how to increment the varying starting
positions thru the InStr() funcion. Is Mid() the fastest way to get the Character Value?

Goal is replacing any constants with a formula Range reverence and re-insert the operators around
the replaced constants. I do NOT need help with IDing the constants and formulizing them.

My challenge is limited to replacing the operators around the Range referrences.

Hope I am clearer now.

Thanks again for your time and knowledge

EagleOne

 

[Dave Peterson]

Instr() allows you to specify the starting position. And Mid() seems like as
good an approach as any.

Option Explicit
Sub OperatorPositionsValueInStr()

Dim OrigStr As String
Dim MainS As String
Dim SubS As String
Dim iCtr As Long
Dim HowManyTildes As Long
Dim StartingPos As Long
Dim Positions() As Variant
Dim myChars As String

myChars = "()/*"
OrigStr = "(972552/2)*3"
SubS = "~"

MainS = OrigStr
For iCtr = 1 To Len(myChars)
MainS = Replace(MainS, Mid(myChars, iCtr, 1), SubS)
Next iCtr

HowManyTildes = Len(MainS) - Len(Replace(MainS, SubS, ""))

If HowManyTildes = 0 Then
MsgBox "no ~'s found!"
Else
ReDim Positions(1 To HowManyTildes, 1 To 2)
StartingPos = 1
For iCtr = 1 To HowManyTildes
Positions(iCtr, 1) = InStr(StartingPos, MainS, SubS, vbTextCompare)
Positions(iCtr, 2) = Mid(OrigStr, Positions(iCtr, 1), 1)
StartingPos = Positions(iCtr, 1) + 1
Next iCtr
ActiveSheet.Range("a1").Resize(HowManyTildes, 2).Value _
= Positions
End If

End Sub

 

[EagleOne]

My rudimentary initial-approach seemed to hold up. That said, my car had square wheels and no axle.
As usual, you solved the challenge in good form while providing an excellent learning experience.
Thanks

EagleOne