Change Column Header that Contains a Word

[bmkelly]

Hello,

I thought a simple google search would do but seems I can't find a specific example for what I am looking for. I have column headers all in row 1. We have multiple templates that all have a header that contains FY21 Impact, FY22 Impact, CY21 Impact, CY22 Impact, OY21 Impact, OY22 Impact and so on. I would like for a code to look in the column headers for the word "Impact" and then change that column Header to "Effective Price"

 

[EFANYoutube]

Hi bmkelly
Give this a go, make sure your on the sheet you want to effect, or to run from a different sheet please reference the sheet instead of ActiveSheet

Sub ChangingStuff()
    For Each cell In ActiveSheet.Range("1:1").Cells
        If InStr(1, cell.Value, "Impact", vbTextCompare) > 0 Then cell.Value = "Effective Price"
    Next cell
End Sub

 

[Joe4]

You can simply just use Find/Replace to do all that at once.

If you want the VBA code for that, it would look like this (no looping required!):

Sub ReplaceHeaderMacro()
    Rows("1:1").Replace What:="Impact", Replacement:="Effective Price", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

 

[bmkelly]

You can simply just use Find/Replace to do all that at once.

If you want the VBA code for that, it would look like this (no looping required!):

Sub ReplaceHeaderMacro()
    Rows("1:1").Replace What:="Impact", Replacement:="Effective Price", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Perfect! I ran it and it did indeed add "Effective Price" as the Header but is there a way to have it remove everything in that header that was previously there and just have it say "Effective Price"? I ran it and for example I have FY22 Impact as the original header, after the code it is now FY22 Effective Price. Sorry I wasn't super clear in my original post!

 

[Joe4]

Just add wildcards around "Impact", i.e.

Sub ReplaceHeaderMacro()
    Rows("1:1").Replace What:="*Impact*", Replacement:="Effective Price", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

 

[bmkelly]

Just add wildcards around "Impact", i.e.

Sub ReplaceHeaderMacro()
    Rows("1:1").Replace What:="*Impact*", Replacement:="Effective Price", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Thanks, sorry I thought I had those already in ?

 

[Joe4]

No worries!

Glad it works for you!