How to Replace Values in a Range Using VBA Without Selection

In this post, I will show how to create a macro that replaces values in a specified range based on a mapping table. The provided VBA script uses a loop to achieve this task efficiently without selecting cells in the workbook.

Setting Up Your Workbook
To prepare for this macro:

1. Create a worksheet named “Sheet1.”
2. In column D, list the values you want to replace (e.g., D2).
3. In columns H and I, create a mapping table where column H contains the values to be replaced, and column I contains their replacements.

Understanding the Code

Here’s a breakdown of the VBA code step by step:

Option Explicit

Sub Button1_Click()
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

1. Option Explicit: Ensures all variables are declared. This reduces errors in your code.
2. Dim i As Integer: Declares a loop variable i.
3. Dim ws As Worksheet: Declares a worksheet object for better readability.
4. Set ws = Worksheets("Sheet1"): Assigns “Sheet1” to the worksheet variable ws.

    For i = 5 To 8
        ws.Range("D2:D15").Replace _
            What:=ws.Cells(i, 8).Value, _
            Replacement:=ws.Cells(i, 9).Value, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False
    Next i

5. For i = 5 To 8: Loops through rows 5 to 8 to process the mapping table.
6. ws.Range("D2:D15").Replace: Searches and replaces values in the range D2.
   • What:=ws.Cells(i, 8).Value: Specifies the value to be replaced (column H).
   • Replacement:=ws.Cells(i, 9).Value: Specifies the replacement value (column I).
   • LookAt:=xlPart: Matches part of the cell content.
   • SearchOrder:=xlByRows: Searches row by row.
   • MatchCase:=False: Makes the search case-insensitive.

   ws.Cells(1, 1).Select
End Sub

7. ws.Cells(1, 1).Select: Moves the selection back to cell A1 after running the macro. This step is optional.

Full Code Example

Option Explicit

Sub Button1_Click()
    Dim i As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    ' Loop through rows 5 to 8 for replacing values
    For i = 5 To 8
        ws.Range("D2:D15").Replace _
            What:=ws.Cells(i, 8).Value, _
            Replacement:=ws.Cells(i, 9).Value, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False
    Next i
    
    ' Move the selection back to cell A1 (optional)
    ws.Cells(1, 1).Select
End Sub

Running the Code

1. Copy the VBA script into a module in your workbook.
2. Assign the macro to a button labeled Replace Values for easy access.
3. Click the button to execute the script and apply the replacements.

Flow Chart Of The Code

Result

Before

After

After running the macro : .The values in the range D2will be replaced based on the mapping defined in columns H and I of rows 5 to 8.

Troubleshooting

• No Changes in Values:
  Ensure the values in column H of rows 5 to 8 exist within the range D2.
• Incorrect Range Replacement:
  Double-check the range specified in ws.Range("D2:D15"). Adjust it to fit your data.
• Errors in Mapping Table:
  Verify that columns H and I contain valid data for replacements.

Conclusion

This VBA script demonstrates an efficient way to replace values in Excel without manually selecting cells. It is highly customizable, making it useful for various tasks like data cleanup and formatting.

For more VBA tips, explore our other posts like How to Automatically Move Data Between Ranges at Intervals in Excel Using VBA