Macro to copy cell from the next column if a certain word is found

Hi,

I'm trying to find a macro that goes through all the rows to see if a cell from Column Z has the word "*other", if so it'll copy the cell next to it in Column AA and replace the original "*other" in Column Z with the copied cell. Otherwise, it would continue to the next row. I've attached a before and after xlsx to give an example of what I'm looking for.

before.xlsx
after.xlsx

Any help would be appreciated. Please let me know if I need to clarify anything.

Thanks.

Try this, but note, it's not column Z in your workbooks that has the "*other", but rather column AA1... so you may have to adjust the range to suit.

Sub Other()
Dim lrow As Long
Dim rng As Range

lrow = ActiveSheet.UsedRange.Rows.Count
Set rng = Range("AA1", "AA" & lrow)

For Each Cell In rng
If Cell.Value = "*other" Then
Cell.Value = Cell.Offset(0, 1).Value
End If
Next Cell
End Sub

If it works for you, I would appreciate some reputation (little black star on the left)

Originally Posted by Raymundus

Try this, but note, it's not column Z in your workbooks that has the "*other", but rather column AA1... so you may have to adjust the range to suit.

Sub Other()
Dim lrow As Long
Dim rng As Range

lrow = ActiveSheet.UsedRange.Rows.Count
Set rng = Range("AA1", "AA" & lrow)

For Each Cell In rng
If Cell.Value = "*other" Then
Cell.Value = Cell.Offset(0, 1).Value
End If
Next Cell
End Sub

If it works for you, I would appreciate some reputation (little black star on the left)

Ah, you're right, I mistakenly copied it into the wrong column. I've adjusted the range to column Z and it worked perfectly! Thank you very much! Repped!