FindAndReplace on multiple columns

Question

I have a workbook with multiple sheets.
The first sheet, "Task", contains the data.
The other sheets are lists for find and replace operations.

I have run the Sub successfully, replacing the data in one column (E) in my first sheet ("Task") with data from other sheets "Salary" & "HR".

Now, I need to perform a find and replace in another column (N) in the "Task" sheet, using data from the "Adm. list" sheet.

This gives me an error message:

Sub FindAndReplace()

    Dim TaskWorksheet As Worksheet
    Dim searchRange As Range
    Dim replaceTable As Variant
    Dim findWhat As String
    Dim replaceWith As String
    Dim i As Long
    
    Set TaskWorksheet = ThisWorkbook.Worksheets("Task")
    
    With TaskWorksheet
        Set searchRange = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
    End With
            
    With ThisWorkbook.Worksheets("HR")
        replaceTable = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
    
    With ThisWorkbook.Worksheets("Adm")
        replaceTable = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
   
    Set TaskWorksheet = ThisWorkbook.Worksheets("Task")
    
    With TaskWorksheet
        Set searchRange = .Range("N2:N" & .Cells(.Rows.Count, "E").End(xlUp).Row)
    End With
            
    With ThisWorkbook.Worksheets("HR list")
        replaceTable = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
     
    For i = 1 To UBound(replaceTable)
        findWhat = replaceTable(i, 1)
        replaceWith = replaceTable(i, 2)
        searchRange.Replace _
            What:=findWhat, _
            replacement:=replaceWith, _
            lookat:=xlPart, _
            MatchCase:=False
    Next i
    
End Sub

"an error message" doesn't help us much - it's best to include the specific message you're getting, and the line where it occurs — Tim Williams
– Tim Williams, Commented Sep 19, 2024 at 23:18

user27385420 · Accepted Answer · 2024-10-03 15:16:50Z

Whenever you have a repetitive task it's best to push each repeated part out into a separate method (Sub/Function). Refactoring your code a little it could work like this:

Sub FindAndReplace()

    Dim FeedbacktaskWorksheet As Worksheet, wb As Workbook
    
    Set wb = ThisWorkbook
    Set TaskWorksheet = wb.Worksheets("Task")
    
    'replacing col E values with list from "HR"
    ReplaceList GetRange(TaskWorksheet.Range("E2")), _
                GetRange(wb.Worksheets("HR").Range("A1"), 2)
        
    'replacing col E values with list from "Salary"
    ReplaceList GetRange(TaskWorksheet.Range("E2")), _
                GetRange(wb.Worksheets("Salary").Range("A1"), 2)
                    
    'replacing col N values with list from "Staff list"
    ReplaceList GetRange(TaskWorksheet.Range("N2")), _
                GetRange(wb.Worksheets("Staff list").Range("A2"), 2)
                    
End Sub

'replace content in `searchrange` using pairs of values from range `replaceTable`
Sub ReplaceList(searchRange As Range, replaceTable As Range)
    Dim rw As Range
    For Each rw In replaceTable.Rows 'loop over pairs of values
        searchRange.Replace What:=rw.Cells(1).Value, _
                            Replacement:=rw.Cells(2).Value, _
                            lookat:=xlPart, MatchCase:=False
    Next rw
End Sub

'Return a range starting at `c` to the end of data in same column,
'   resized to specified number of columns (# of cols defaults to 1)
'Helper Function to avoid all those `.End(xlUp)` calls
Function GetRange(c As Range, Optional numCols As Long = 1) As Range
    With c.Parent
        Set GetRange = .Range(c, .Cells(.Rows.Count, _
                        c.Column).End(xlUp)).Resize(, numCols)
    End With
End Function

Collectives™ on Stack Overflow

FindAndReplace on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related