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!

Your code is performing a replacement on cell B1048576 in the active sheet, and only if that cell contains "Global Macro" — ThunderFrame
– ThunderFrame, Commented Jun 1, 2017 at 3:15

ThunderFrame · Accepted Answer · 2017-06-01 03:24:27Z

14

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"

edited Jun 1, 2017 at 3:24

answered Jun 1, 2017 at 3:17

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eugene Foo Over a year ago

Do you mean to replace the entire code above with this single statement?

ThunderFrame Over a year ago

Yes. You could narrow it down to the number of used rows, but assuming there isn't any data below or above the range you need values replaced in, the line above should suffice. The important thing is that you use the Range.Replace method rather than the VBA.Replace function.

Eugene Foo Over a year ago

ah ok, thanks for the tip. However, I've tried the code and this error message pops up: "Compile error: Expected function or variable"

ThunderFrame Over a year ago

If you use the line between a Sub ReplaceFundNames() line, and an End Sub line, I can't see any way that you can get a compile error. Are you certain you haven't made a typo, and/or that you don't have an error elsewhere in your code? ActiveSheet.Range("B:B").Replace "Global Macro", "GM" will work, unless you don't have an active sheet.

Eugene Foo Over a year ago

ok, I tried it on a new spreadsheet, works like a charm, thanks so much!

Collectives™ on Stack Overflow

VBA Excel find words in a range and replace

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related