VBA - Loop Through Tabs, Find Header by Name, Replace Values

[alexb523]

i have 4 tabs in a workbook that i would like to loop through, find the header "ClaimName", which resides in different location IF it exists at all, and then replace the values "Claim1Score", "Claim12Score", "Claim3Score", "Claim4Score" with "Claim 1", "Claim 2" ... etc.
I am rusty with vba. I have found similar questions around, but cannot seem to quite get them to work out.
Hope you can help! Thanks!

 

[JoeMo]

This assumes you have, at most, one header "ClaimName" on any worksheet in your workbook. Note: You used an inconsistent format in your OP - sometimes showing a space between Claim and the first numeral (e.g. Claim 1Score) and sometimes no space (e.g. Claim4Score), but with the end result always showing a space. I assumed here the space is what you actually have.

Sub ChangeClaim()
Dim Sht As Worksheet, R As Range
For Each Sht In ThisWorkbook.Sheets
    Set R = Sht.Cells.Find("ClaimName", , xlValues, xlWhole)
    If R Is Nothing Then
        GoTo Nx
    Else
        Sht.Range(R, Sht.Cells(Sht.Rows.Count, R.Column).End(xlUp)).Replace "Score", "", xlPart
    End If
Nx:
Next Sht
End Sub

 

[alexb523]

thanks JoeMo. this works great. i actually do not have a space between 'claim' and first numeral. I have added the following to the for loop to get the space (sh2 = Sht for consistency in other parts of code). Please let me know if there is a more elegant solution.

sh2.Range(R, sh2.Cells(sh2.Rows.Count, R.Column).End(xlUp)).Replace "Claim", "Claim ", xlPart

thanks, alex