I am having a diffucult time performing a regular expression on the contents of a single range.
I know the formula will be a simple sum function similiar to SUM(B191)/19.
What I need to do is dynamically change the divisor (19) when the user enters values in certain cells. All is going ok except for actually performing the regex.
What I thought of doing was to match everything up to and including the /, Assign it to a viariable, reuse that variable with my new divisor and throw the new formula in the cell.
the regex section follows:
- 'Reference set to Microsoft VbScript Regular Expression 5.5
- Dim RegEx As Object
- Dim RegMatchCollection As Object
- Dim RegMatch As Object
- Dim SubMatch As Object
- Dim Item As Variant
- Dim TestStr As String
- Dim i As Long
- TestStr = FirstFormula
- MsgBox "Regex TestString is " & TestStr & "", vbExclamation, ""
- ' create the RegExp Object
- Set RegEx = CreateObject("vbscript.regexp")
- ' set the RegExp parameters
- With RegEx
- .MultiLine = False
- .Global = True
- .IgnoreCase = True
- .Pattern = "=SUM"
- End With
- MsgBox "Regex is " & RegEx.Pattern & "", vbExclamation, ""
- ' create the collection of matches
- Set RegMatchCollection = RegEx.Execute(TestStr)
- ' loop through each Match in the collection
- For Each RegMatch In RegMatchCollection
- i = i + 1
- ' create the Sub Match collection
- Set SubMatch = RegMatch.SubMatches
- ' loop through each Sub Match
- For Each Item In SubMatch
- MsgBox "Match " & i & " contains submatch " & Item & "", vbExclamation, ""
FirstFormula was captured earlier, it is the contents for the first cell to change.
I have simplified the RegEx.pattern to a straight string.
I'm not a regex expert in Vb and can't seem to get the objects to work the way I want ... MUCH appreciated for any help ...