Forum Discussion

InThought's avatar
InThought
Copper Contributor
Dec 12, 2024

Rename Cell Contents

Have given this macro a shot, but it isn't working:

Sub StandardizePayee()
    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(i, "B").Value = "Marathon" Then
            Cells(i, "B").Value = "Marathon Gas"
        End If
    Next i
End Sub

Would like to standardize payees for search purposes.

Excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 14, 2024

    What exactly is the problem?

    • The macro produces an error - if so, what does it say?
    • The macro does nothing at all.
    • The macro produces an incorrect result - if so, what?

    This should be more efficient:

    Sub StandardizePayee()
    Range("B:B").Replace What:="Marathon", Replacement:="Marathon Gas", LookAt:=xlWhole
End Sub
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Dec 15, 2024

    Try this:

     

    Sub StandardizePayee()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SheetName") ' Replace "SheetName" with your actual sheet name

    For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If ws.Cells(i, "B").Value = "Marathon" Then
            ws.Cells(i, "B").Value = "Marathon Gas"
        End If
    Next i
End Sub

     

Resources