I’m new to VBA.
I have a workbook with multiple sheets. The first sheet, “feedbacktask”, contains the data I want to work with, while the other sheets are lists that I want to use for find and replace operations.
I have successfully run the Sub, replacing the data in one column (E) in my first sheet (“Feedbacktask”) with data from other sheets “Payroll” & “askHR”. Now, I need to perform a find and replace in another column (N) in the “Feedbacktask” sheet, using data from the “author list” sheet.
How can I do this?
This is how it looks so far, but it gives me an error message. If I only run it for one column (E) it works and replaces the text in the column (E) with the text from my lists in sheets “Payroll” & “askHR” :
Sub FindAndReplace()
Dim FeedbacktaskWorksheet As Worksheet
Dim searchRange As Range
Dim replaceTable As Variant
Dim findWhat As String
Dim replaceWith As String
Dim i As Long
Set FeedbacktaskWorksheet = ThisWorkbook.Worksheets("Feedbacktask")
With FeedbacktaskWorksheet
Set searchRange = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
End With
With ThisWorkbook.Worksheets("askHR DK")
replaceTable = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With
With ThisWorkbook.Worksheets("Payroll")
replaceTable = .Range("A1:B" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With
Set FeedbacktaskWorksheet = ThisWorkbook.Worksheets("Feedbacktask")
With FeedbacktaskWorksheet
Set searchRange = .Range("N2:N" & .Cells(.Rows.Count, "E").End(xlUp).Row)
End With
With ThisWorkbook.Worksheets("Author 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
New contributor
user27385420 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
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 FeedbacktaskWorksheet = wb.Worksheets("Feedbacktask")
'replacing col E values with list from "askHR DK"
ReplaceList GetRange(FeedbacktaskWorksheet.Range("E2")), _
GetRange(wb.Worksheets("askHR DK").Range("A1"), 2)
'replacing col E values with list from "Payroll"
ReplaceList GetRange(FeedbacktaskWorksheet.Range("E2")), _
GetRange(wb.Worksheets("Payroll").Range("A1"), 2)
'replacing col N values with list from "Author list"
ReplaceList GetRange(FeedbacktaskWorksheet.Range("N2")), _
GetRange(wb.Worksheets("Author 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
3