VBA find and replace under multiple conditions

[chris54]

Hi there,

I want to create a macro where I can find and replace the wording of my data in one column depending on the condition of another.

I would like to search column A for "Scotland" and "Southern" then if column J is "SHIPPER" change column A to "Scotland Transport" or "Southern Transport"

Is anyone able to suggest a way to do this? I've been tinkering but anything I try seems to fail

 

[chris54]

Sub Replace_data1()
'
' Replace_data1 Macro
'

'

Sheets("TEST").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("J" & Rows.Count).End(xlUp).Row


For i = 1 To LastRow
If Range("J" & i).Value = "SHIPPER" & Range("A" & i).Value = "Scotland" Then
Range("A" & i).Value = "Scotland TRANSPORT"
End If
Next i

End Sub

This is what I have so far

 

[Akuini]

Try:

Sub chris54()
'I would like to search column A for "Scotland" and "Southern" then if column J is "SHIPPER"
'change column A to "Scotland Transport" or "Southern Transport"

Dim c As Range, x
Dim sAddress As String
Application.ScreenUpdating = False
For Each x In Array("Scotland", "Southern")
    With Range("A:A")
    Set c = .Find(What:=x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           If Cells(c.Row, "J") = "SHIPPER" Then
                c = x & " Transport"
           End If
        Loop While Not c Is Nothing And c.Address <> sAddress
    End If
    End With
Next
Application.ScreenUpdating = True
End Sub

 

[Akuini]

Another way to try:

Sub chris54_A()
Dim n As Long, i As Long
Dim va, vb
n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A1:A" & n)
vb = Range("J1:J" & n)

For i = 2 To UBound(va, 1) 'data start at row 2
    If vb(i, 1) = "SHIPPER" Then
        If va(i, 1) = "Scotland" Then
            va(i, 1) = "Scotland Transport"
        ElseIf va(i, 1) = "Southern" Then
            va(i, 1) = "Southern Transport"
        End If
    End If
Next

Range("A1").Resize(UBound(va, 1), 1) = va
End Sub

 

[chris54]

Try:

Sub chris54()
'I would like to search column A for "Scotland" and "Southern" then if column J is "SHIPPER"
'change column A to "Scotland Transport" or "Southern Transport"

Dim c As Range, x
Dim sAddress As String
Application.ScreenUpdating = False
For Each x In Array("Scotland", "Southern")
    With Range("A:A")
    Set c = .Find(What:=x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           If Cells(c.Row, "J") = "SHIPPER" Then
                c = x & " Transport"
           End If
        Loop While Not c Is Nothing And c.Address <> sAddress
    End If
    End With
Next
Application.ScreenUpdating = True
End Sub

LEGEND!! Thank you for the help!!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.