TechHelpList.com

Sometimes you want to filter data in Excel in a way that only a good Regular Expression can do. There are several RegExp-type objects/libraries available to VBScript, but there are fewer visible to VBA.

In this example, I want to parse all the digits and chars in a row, putting the digits in the cell value and the a-z characters in the cell comments.

Necessary for this:

Looping through cells in a row in VBA.

Regular Expressions in VBA.

Accessing some Excel VBA API, cell properties .value and .comments and cell method .addcomments().

To Loop thru Row 4, a column at a time (cell), putting chars in comments and numbers in value.

Sub char2comment()


Dim RENums As Object
Dim REChars As Object
Set RENums = CreateObject("VBScript.RegExp")
Set REChars = CreateObject("VBScript.RegExp")

Dim matchNums As Object
Dim matchChars As Object

Dim validNums As Boolean
Dim validChars As Boolean

RENums.Pattern = "[0-9]+"
REChars.Pattern = "[a-zA-Z]+"

Dim cellInRow As Range

For Each cellInRow In Range("4:4")
   
    If cellInRow.Value = "" Then
        Exit For
    End If
    
   'you really gotta test these regular expressions first.
   'because matchChars will NOT be nothing but still
   'will throw an error if you try to cstr(it(0)). it exists,
   'isn't empty, but can't be dereferenced.

    validNums = RENums.Test(cellInRow.Value)  
    If validNums = True Then
        Set matchNums = RENums.Execute(cellInRow.Value)
    End If
    
    validChars = REChars.Test(cellInRow.Value)
    If validChars = True Then
        Set matchChars = REChars.Execute(cellInRow.Value)
    End If
    
    If Not matchChars Is Nothing Then
        cellInRow.ClearComments
        cellInRow.AddComment (CStr(matchChars(0)))
    End If
    
    
    If Not matchNums Is Nothing Then
        cellInRow.ClearContents
        cellInRow.Value = CStr(matchNums(0))
    End If
    
    Set matchChars = Nothing
    Set matchNums = Nothing
    
    validNums = False
    validChars = False
    
    
Next cellInRow


MsgBox "fin"
End Sub

If this article was at least a little helpful, how about a +1?