mercredi 12 juin 2019

Find Replace Ifcondition

I wanna use the vba find and replace with an IF statement. I have got the find and replace part, but I only want excel to change the values if column A has "2019".

I don't know how to incorporate the If statement in my code.

Sub TypeBusinessReplace()

Dim myDataSheet As Worksheet
Dim myReplaceSheet As Worksheet
Dim myLastRow As Long
Dim myRow As Long
Dim myFind As String
Dim myReplace As String
'   Specify name of Data sheet
Set myDataSheet = Sheets("sheet2")
'   Specify name of Sheet with list of replacements
Set myReplaceSheet = Sheets("sheet1")
'   Assuming list of replacement start in column B on row 2, find last 
entry in list
myLastRow = myReplaceSheet.Cells(Rows.Count, "E").End(xlUp).Row
Application.ScreenUpdating = False
'   Loop through all list of replacments

For myRow = 2 To myLastRow
'       Get find and replace values (from columns B and C)
myFind = myReplaceSheet.Cells(myRow, "E")
myReplace = myReplaceSheet.Cells(myRow, "H")
'       Start at top of data sheet and do replacements
myDataSheet.Activate
Range("E1").Select
'       Ignore errors that result from finding no matches
On Error Resume Next
'       Do all replacements on column A of data sheet
Columns("Q:Q").Replace What:=myFind, Replacement:=myReplace, 
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'       Reset error checking
On Error GoTo 0
Next myRow
Application.ScreenUpdating = True
MsgBox "Replacements complete!"
End Sub

Aucun commentaire:

Enregistrer un commentaire