Excel Macro Search and Replace Cell

Question

Please help i want to clean my data in excel with macro.

Before:

After:

So i need suggestion function macro to search DATA with paramater in MASTER. After that, macro can be show result what parameter match with this DATA

EDIT NOTE : i just want to know what function i can be used in macro for this question. Not to request full macro for my questions. Sorry for all

Loop over your data, `Split` your data or use `RegEx` to identify which element in your master array fits your data. If you have some code written, than edit your current question. — JvdV, Jan 27 '20 at 10:06
If you need a macro you need to start writing one, because this is no free code writing service. It is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. — Pᴇʜ, Jan 27 '20 at 10:25
To all, i just want to know what function i will be used to create macro for my questions, after that i will share my macro here if i stuck in macro excel — Dinda, Jan 27 '20 at 10:29
@Dinda You need to use the `Split` function to split each cell value of column A by space into its words. Then lookup each word in the master column using `Application.WorksheetFunction.VLookup` and if it was found write it into column B. — Pᴇʜ, Jan 27 '20 at 10:37
As per my first comment, `Split` (as demonstrated by @Pᴇʜ) or `RegEx` is what you could use. Dump your master parameters into a 1D-array (e.g. `arr1`), then build a pattern to use in `RegEx`, for example: `.Pattern = "\b(" & Join(arr1, "|") & ")\b"`. Execute your regex in a loop over your data. — JvdV, Jan 27 '20 at 10:39
Here is an [Example](https://regex101.com/r/INPq0y/1) for the RegEx pattern that @JvdV suggested. Make sure you use the case insensitive switch `/i` to regognize `MERCY` as `Mercy`. • Use [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) for a how to. Generate the pattern on the fly using VBA. — Pᴇʜ, Jan 27 '20 at 10:47
@Pᴇʜ, `/i` is not supported within `VBA`. Instead use set `IgnoreCase` property to `TRUE`. [Here](https://stackoverflow.com/q/59579008/9758194) is a link =) — JvdV, Jan 27 '20 at 10:50
@JvdV yes that is what I actually meant with *"use the case insensitive switch"*, sorry bad explanation. — Pᴇʜ, Jan 27 '20 at 10:51

CDP1802 · Accepted Answer · 2020-01-27T13:21:26.650

Build the regular expression by transposing the master range and joining the values with |. Test the regex against each cell value in DATA and if successful execute the regex to capture the value.

  Sub clean()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)
    Dim rngMASTER as Range, rngDATA As Range
    Dim start as Single, finish as Single
    start = Timer

    ' set ranges
    Dim numLastA as Long, numLastC As Long
    numLastA = ws.Range("A" & Rows.count).End(xlUp).Row
    Set rngDATA = ws.Range("A2:A" & numLastA)

    numLastC = ws.Range("C" & Rows.count).End(xlUp).Row
    Set rngMASTER = ws.Range("C2:C" & numLastC)
    'Debug.Print numLastA, numLastC

    ' avoid blanks in pattern
    If WorksheetFunction.CountBlank(rngMASTER) > 0 Then
      MsgBox "MASTER range has blank cells", vbCritical
      Exit Sub
    End If

    ' build regex pattern
    Dim sPattern As String
    sPattern = Join(WorksheetFunction.Transpose(rngMASTER), "|")
    'Debug.Print sPattern

    Dim Regex as Object
    Set Regex = CreateObject("vbscript.regexp")

    With Regex
      .Global = True
      .MultiLine = False
      .IgnoreCase = True
      .Pattern = "(" & sPattern & ")"
    End With

    ' search
    Dim cell as Range, match as Object
    Dim count As Long: count = 0

    For Each cell In rngDATA
      If Regex.test(cell) Then
        Set match = Regex.Execute(cell)
        cell.Resize(1, 3).Copy cell.Offset(0, 5)
        cell.Offset(0, 6) = match(0).submatches(0)
        count = count + 1
      End If
    Next

    finish = Timer
    MsgBox numLastA - 1 & " rows scanned" & vbCr & _
    count & " matches in " & Int(finish - start) & " secs"
  End Sub

Edit 1; Added protection against a blank cell in MASTER which would match every row Edit 2 ; Declared type for all variables

Please note that if you declare `Dim rngMASTER, rngDATA As Range` you only declare `rngDATA As Range` but `rngMASTER As Variant` in VBA you need to specify a type for **every** variable: `Dim rngMASTER As Range, rngDATA As Range` otherwise it is `Variant` by default. • Same for other variables `Dim numLastA, numLastC As Long` check all of them. Also it is better not to use `Variant` if possible so eg `Dim Regex As Object` would be a better solution. And your timer variables `start, finish` could be `Single`. `cell As Range`, `match As Object`. `count` should be `Long` instead of `Integer` — Pᴇʜ, Jan 27 '20 at 12:45

Excel Macro Search and Replace Cell

1 Answers1