Find and Replace with nested If?

[kvarner]

I can do a find and replace in VBA, but I'm not sure how to write this one with the If thrown in.
Basically what I need to do is: IF Column H.Value = TravelMeal OR Vehicle Shipping
Then
IF Column G.Value = "18 MDX" Then Update Column G.Value = "2BJ"
Else Do Nothing.

There are many instances where Column H is either TravelMeal or Vehicle Shipping, But I only want to change the value in Column G if it is currently 18 MDX and the former is true.

Hope that makes sense, appreciate any help!

[stnkynts]

Try this:

Sub kvarner()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")

Application.ScreenUpdating = False
With ws
    .AutoFilterMode = False
    .Range("A1:H" & .Range("H" & Rows.Count).End(xlUp).Row).AutoFilter 8, "TravelMeal", xlOr, "Vehicle Shipping"
    .Range("A1:H" & .Range("H" & Rows.Count).End(xlUp).Row).AutoFilter 7, "18 MDX"
    .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Value = "2BJ"
    .AutoFilterMode = False
End With
Application.ScreenUpdating = True
    
End Sub

[Richard Buttrey]

Hi,

Rather than using looping code in a macro which is often slow I'd be inclined to use a helper column and enter the following formula

=IF(AND(G1="18 MDX",OR(H1="TravelMeal",H1="Vehicle Shipping")),"2 BJ",G1)

Copy this down the helper column then copy the helper range and paste it back to G1 as values.

You could of course encapsulate that process in a simple macro to automate the same process.

[kvarner]

I have a lot of other code in this VBA function, so I had to modify it slighty. But I'm getting a user defined error. I'm guessing I'm missing something simple.

With Worksheets("Other Accounts")
.Range ("A1:H" & .Range("H" & Rows.Count).End(x1Up).Row), "Travel_Meal", x10r, "Vehicle_Shipping"
.Range ("A1:H" & .Range("H" & Rows.Count).End(x1Up).Row), "18 MDX"
.Range("G2:G" & .Range("G" & Rows.Count).End(x1Up).Row).SpecialCells(x1CellTypeVisible).Value = "2BJ"
End With

[kvarner]

There are about 65 different files that I'm running this code on so there's no way I can reasonably use the helper column idea. Although it is a good one, if it were only one workbook that's definitely the route I would take! Thank you.

[stnkynts]

I have a lot of other code in this VBA function, so I had to modify it slighty. But I'm getting a user defined error. I'm guessing I'm missing something simple.

You left a lot of the stuff out from the code I supplied. Compare your code and my code for the differences.

[kvarner]

I didn't want to turn off the screen updating until I'm done. I'm not sure what the auto filter does.
But I think I found another solution while I've been playing with it, although it does give me an error too.

Dim i As Long
For i = 1 To Rows.Count
If (Cells(i, 5).Value = "Travel_Meal") Or (Cells(i, 5).Value = "Vehicle_Shipping") And Cells(i, 6).Value = "18 MDX" Then
Cells(i, 5).Value = "2BJ"

End If

Next i

[kvarner]

This doesn't give me an error, but it doesn't change any values either. I'm guessing it has something to do with my parenthesis?

Dim i As Long
For i = 1 To Rows.Count
If (Cells(i, 5).Value = "Travel_Meal" Or Cells(i, 5).Value = "Vehicle_Shipping" And Cells(i, 6).Value = "18 MDX") Then
Cells(i, 5).Value = "2BJ"

End If

Next i

[kvarner]

Nevermind, the above code works perfectly! Apparently I just don't know how to count my columns. Ha.

Thanks again!