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, "" Next Next
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 ...