Find and Replace

Question

I had written a macro that was working fine but it looks like there was some sort of update and now my code isn't working. Can anyone help me identify what's going wrong or why this function would no longer work?

Here is the function as it is:

Function FindReplace(CellValue$)
    Dim strPattern$: strPattern = "[^A-Za-z, ]+"    'Pattern to only take care of letters
    Dim strReplace$: strReplace = ""    'Replace everything else with blank
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function

I'm trying to have it look at a a cell and only allow specific characters to surface.

Here is the larger code that this function is a part of:

'Concatenate all the data in the rows into columns A
    Sheets("Formula2").Select
    Dim Lastrow%: Lastrow = ActiveSheet.UsedRange.Rows.Count
    Dim strConcatenate$, I%, j%
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = ActiveSheet

    Range("A:A").Clear

    For I = 1 To Lastrow
        For j = 2 To lastColumn(I) 'Calls function "LastColumn" to get the last column of each row
            strConcatenate = strConcatenate & FindReplace(ws.Cells(I, j))
        Next j
        ws.Cells(I, 1) = strConcatenate 'This will past the finished string into column [A] in the specific row
        strConcatenate = "" 'blanks the string, so the next string in the next row is fresh
    Next I

*"code isn't working"* is no useful error description. Please tell which error you get and where, or what your code does vs what you expect your code should actually do. Also please provide a [mcve] and valid input/output example data (of what you expect, and of what your code does). — Pᴇʜ, Oct 30 '18 at 15:11
If I have a cell with the value "Test", when I use this formula the value becomes "#VALUE!" rather than remain as "Test". — Valborg, Oct 30 '18 at 15:13
Call your function from a Sub and not from a worksheet - you will then be able to debug — Tim Williams, Oct 30 '18 at 15:15
The code works as intended for me (cannot reproduce it). What is the exact formula in the cell that calls that function? — Pᴇʜ, Oct 30 '18 at 15:17
@cybernetic.nomad not needed because of Late Binding (`CreateObject("vbscript.regexp")`). — Pᴇʜ, Oct 30 '18 at 15:21
So the code is two parts. It first concatenates all the data in the columns into column 1 for the respective row. Then it takes that concatenated data and 'cleans' it. So for example. If I had in row 1, A "", B "Test", C "123". Then it would put the value "Test123" into cell A1 and then clean it to just say "Test" — Valborg, Oct 30 '18 at 15:26
That gave me "Compile error: ByRef argument type mismatch" when it got to For j = 2 To lastColumn(I) — Valborg, Oct 30 '18 at 15:33
I tested your code with A "", B "Test", C "123" and it did exactly what you said. It wrote "Test" into A1 (I just replaced `lastColumn(I)` by `3` for testing). Avoid using `.Select` and `ActiveSheet` (see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)) instead reference your sheet by name. — Pᴇʜ, Oct 30 '18 at 15:33
So change `lastColumn(I)` to use `Long` data types also then (note that call doesn't appear anywhere in your posted code). You shouldn't be using `Integer` *anywhere* when you're dealing with rows. — Comintern, Oct 30 '18 at 15:39

Pᴇʜ · Answer 1 · 2018-10-30T15:48:27.530

I suggest the following code (tested with A "", B "Test", C "123").

Note that you need to adjust the For j = 2 To 3 loop to end at your last column.

Option Explicit

Sub Test()
    'Concatenate all the data in the rows into columns A
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Formula2") 'avoid select and specify the sheet by name

    Dim LastRow As Long
    LastRow = ws.UsedRange.Rows.Count

    Dim strConcatenate As String
    Dim i As Long, j As Long

    ws.Range("A:A").Clear 'always specify in which sheet a range is!

    For i = 1 To LastRow
        For j = 2 To 3 'changed that for testing to 3
            strConcatenate = strConcatenate & FindReplace(ws.Cells(i, j).Value)
        Next j
        ws.Cells(i, 1).Value = strConcatenate 
        strConcatenate = "" 
    Next i
End Sub

Function FindReplace(CellValue As String) As String
    Dim strPattern As String
    strPattern = "[^A-Za-z, ]+"    'Pattern to only take care of letters

    Dim strReplace As String
    strReplace = ""    'Replace everything else with blank

    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function

Find and Replace

1 Answers1