Find and replace under conditions using VBA

[chris54]

Hi there,
I'm trying to create a macro in VBA to search through large amounts of data and change the wording depending on the condition.

I want to search column J for "SHIP" and if so, change column A to "TRANSPORT".

I've been fiddling with the following but can't get it working.

Can anyone help?


Dim x As Integer
Dim i As Integer

x = Range("J" & Rows.Count).End(xlUp).Row

For i = 1 To x

If Range("J" & i).Value = "SHIPPER" Then
Range("A" & i).Value = "TRANSPORT"
End If
Next i

 

[Joe4]

Welcome to the Board!

I want to search column J for "SHIP"

If Range("J" & i).Value = "SHIPPER" Then

Which is it? Are you checking for "SHIP" or "SHIPPER"?
Is that the issue?

Also, are the values in column J EXACTLY equal to the value you are looking for, with no other characters (including) spaces in those cells?
Note that something as simple as an extra space will cause it NOT to match.

If you still cannot figure it out, please post a small sample of your data.

 

[chris54]

Welcome to the Board!




Which is it? Are you checking for "SHIP" or "SHIPPER"?
Is that the issue?

Also, are the values in column J EXACTLY equal to the value you are looking for, with no other characters (including) spaces in those cells?
Note that something as simple as an extra space will cause it NOT to match.

If you still cannot figure it out, please post a small sample of your data.

Hi,

Sorry I'm searching for "SHIPPER" in Column J and want to change the corresponding rows to be "TRANSPORT" in column A. The values in column J are exactly equal to what I'm looking for but I'm relatively new to VBA so struggling to perfect it.

 

[Joe4]

Your code works perfectly fine for me.

What is the name of the module have you placed this code?

If you place this formula in cell K4, what does it return?

=LEN(J4)