asked on

Need help with a RegEx function in Excel 2003

Please take a look at the attached workbook. I need to replace the word "ActiveCell" in the RegEx function "GetSeats" so that it will calculate and re-calculate correctly for all cells containing the function. I tried using "InputCell," but that didn't work. I don't know enough about Regular Expressions to know what in the function code refers to the cell.

Also, when you have two row groupings in the cell being referenced by the function (such as ROWS 9-11, ROWS 35-36), there should be a comma between the resulting strings, but there isn't.

Thanks,
John

Function GetSeats(InputCell As Range) As String
    Dim RegEx, RegM, RegMC
    Dim MyDic
    Dim tmpStr As String
    Dim i As Long
 
    Set MyDic = CreateObject("scripting.dictionary")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Pattern = "ROW(S)? (\d+)-(\d+)"
        .Global = True
        .IgnoreCase = True
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If MyDic.exists(LCase$(RegM)) = False Then
                    For i = RegM.submatches(1) To RegM.submatches(2)
                        Select Case i
                        Case Is <= ActiveCell.Offset(0, 96)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 111) & ", "
                        Case Is <= ActiveCell.Offset(0, 98)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 112) & ", "
                        Case Is <= ActiveCell.Offset(0, 100)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 113) & ", "
                        Case Else
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 114) & ", "
                        End Select
                    Next
                    tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
                    MyDic.Add LCase$(RegM), 1
                End If
            Next
        End If
        .Pattern = "\d+[A-M]+"
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If tmpStr = vbNullString Then
                    tmpStr = RegM
                Else
                    tmpStr = tmpStr & ", " & RegM
                End If
            Next
        End If
        GetSeats = tmpStr
    End With
End Function

Open in new window

RegEx3.xls

Dave

John,

You can add a second cell ref to replace activecell. Plus then update the OFFSET logic, see below

So AD11 then becomes
=IF(T11=0,MID(AV11,BG11,4),IF(T11=T9,"",GetSeats(T11,T1)))

to anchor T1 as the OFFSET reference

I didn't understand your "," point. Can you psl expand

Cheers

Dave

Function GetSeats(InputCell As Range) As String   ' 'brettdj
    Dim RegEx, RegM, RegMC
    Dim MyDic
    Dim tmpStr As String
    Dim i As Long
 
    Set MyDic = CreateObject("scripting.dictionary")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Pattern = "ROW(S)? (\d+)-(\d+)"
        .Global = True
        .IgnoreCase = True
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If MyDic.exists(LCase$(RegM)) = False Then
                    For i = RegM.submatches(1) To RegM.submatches(2)
                        Select Case i
                        Case Is <= ActiveCell.Offset(0, 96)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 111) & ", "
                        Case Is <= ActiveCell.Offset(0, 98)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 112) & ", "
                        Case Is <= ActiveCell.Offset(0, 100)
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 113) & ", "
                        Case Else
                            tmpStr = tmpStr & i & ActiveCell.Offset(0, 114) & ", "
                        End Select
                    Next
                    tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
                    MyDic.Add LCase$(RegM), 1
                End If
            Next
        End If
        .Pattern = "\d+[A-M]+"
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If tmpStr = vbNullString Then
                    tmpStr = RegM
                Else
                    tmpStr = tmpStr & ", " & RegM
                End If
            Next
        End If
        GetSeats = tmpStr
    End With
End Function

Open in new window

RegEx3a.xls

John Carney

ASKER

Hi Dave, I'll check this out when I get back to work tomorrow. I just opened the workbook that I attached and it's not working the way it was on my computer., so here's a revised version. It explains the caomma thing.
Thanks,
John

RegEx4.xls

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Great, Dave, thanks. I understand this well enough that I modified the cases so that they are all based on the RefCell.
I would like to understand it better of course. Can you point out some specific values in my actual workbook that will help me understand what's going on in this line?
For i = RegM.submatches(1) To RegM.submatches(2)
What part of T6 is captured in RegM.submatches(1)?
What part of T6 is captured in RegM.submatches(2)?
Same for T8.
What exactly is the function doing with cells T6 and T8 to produce the results it produces?
Thanks,
John

For i = RegM.submatches(1) To RegM.submatches(2)
                        Select Case i
                        Case Is <= RefCell
                            tmpStr = tmpStr & i & RefCell.Offset(0, 15) & ", "
                        Case Is <= RefCell.Offset(0, 2)
                            tmpStr = tmpStr & i & RefCell.Offset(0, 16) & ", "
                        Case Is <= RefCell.Offset(0, 4)
                            tmpStr = tmpStr & i & RefCell.Offset(0, 17) & ", "
                        Case Else
                            tmpStr = tmpStr & i & RefCell.Offset(0, 18) & ", "
                        End Select
                    Next

Open in new window