#2 Regex Find and Replace (Not as formula)

[epicode]

I use regex to search and replace text using vba. it comes in really handy and extremely powerful.
what I have created is a rather crude form of vba.
It would be nice to have it as part of EEA with proper options and toolbar buttons etc.

sharing my code which will give and idea and/or help others who may be looking for something similar.
Note: Code is only Regex Search (replace is not implemented).

Public pattern As String

Sub RegexFind()

    Dim rng As Range
    Dim cell As Range
    Dim regex As Object

    ' following check searches in a selection if selection is more than a single cell else it searches in whole sheet
    If Selection.Count > 1 Then
        Set rng = Selection
    Else
        Set rng = ActiveSheet.UsedRange
    End If

    ' asks for a pattern if pattern does not exist preset manually in Immediate window in VBA editor window by typing: pattern = "")

    Set regex = CreateObject("VBScript.RegExp")
    If pattern = "" Then
        pattern = InputBox("Enter Pattern", "", pattern)
    End If
    regex.pattern = pattern
    For Each cell In rng
        ' following check skips all cells until active cell for a given range
        If cell.Row < ActiveCell.Row And cell.Column < ActiveCell.Column Then
            GoTo skipiteration
        End If
        If regex.Test(cell.value) Then
            cell.Activate
            Application.SendKeys "{F2}"  'sets editing mode on for matched cell
            Exit Sub
        End If
skipiteration:
    Next
    MsgBox ("Finished Searching")

End Sub

[SteveT]

Thanks. I have incorporated your two requests. I don't know when I will release a new version, but if you want to try this out, I can send you an advance copy.
Steve
On Tuesday, July 11, 2023 at 03:20:42 PM CDT, M MOHSIN mohsyn@users.sourceforge.net wrote:

[feature-requests:#2] Regex Find and Replace (Not as formula)

Status: open
Group: Next_Release_(example)
Created: Tue Jul 11, 2023 08:20 PM UTC by M MOHSIN
Last Updated: Tue Jul 11, 2023 08:20 PM UTC
Owner: nobody

I use regex to search and replace text using vba. it comes in really handy and extremely powerful.
what I have created is a rather crude form of vba.
It would be nice to have it as part of EEA with proper options and toolbar buttons etc.

sharing my code which will give and idea and/or help others who may be looking for something similar.
Note: Code is only Regex Search (replace is not implemented).
Public pattern As String

Sub RegexFind()

Dim rng As Range
Dim cell As Range
Dim regex As Object

' following check searches in a selection if selection is more than a single cell else it searches in whole sheet
If Selection.Count > 1 Then
    Set rng = Selection
Else
    Set rng = ActiveSheet.UsedRange
End If

' asks for a pattern if pattern does not exist preset manually in Immediate window in VBA editor window by typing: pattern = "")

Set regex = CreateObject("VBScript.RegExp")
If pattern = "" Then
    pattern = InputBox("Enter Pattern", "", pattern)
End If
regex.pattern = pattern
For Each cell In rng
    ' following check skips all cells until active cell for a given range
    If cell.Row < ActiveCell.Row And cell.Column < ActiveCell.Column Then
        GoTo skipiteration
    End If
    If regex.Test(cell.value) Then
        cell.Activate
        Application.SendKeys "{F2}"  'sets editing mode on for matched cell
        Exit Sub
    End If

skipiteration:
Next
MsgBox ("Finished Searching")

End Sub

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/essenexceladdin/feature-requests/2/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[epicode]

Sure.