VBA Excel find words in a range and replace

Question

I want to replace words in a column with another word, however my code does not seem to be working, I'm still trying to get a grip on the coding language, but it's just not intuitive to me.

Situation:

Global Macro - Equities
Global Macro - Bonds
Global Macro - FX
.
.
.
And so on...

Desired outcome:

GM - Equities
GM - Bonds
GM - FX
.
.
.

My code

Sub ReplaceFundNames()

    Dim FundName As String
    Dim CorrectedName As String
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ActiveSheet
    LastRow = Range("B" & Rows.Count).End(xlDown).Row

    FundName = Range(LastRow)
    CorrectedName = Replace(FundName, "Global Macro", "GM")

    Range("B" & LastRow).Value = CorrectedName


End Sub

Thank you!

Answer 1

The Replace function in VBA is designed to work on a single string, whereas the Range.Replace method is designed to work on a Range.

As such, you can perform your replacements in a single statement:

ActiveSheet.Range("B:B").Replace "Global Macro", "GM"