CODEWITHSUNDEEP
excelvbaexcel-formula
Rav
Rav

Reputation: 1387

Replace reference to worksheet in a formula via macro

I have a button which produces a new worksheet. I pass the name of the active worksheet (the origin of the clicked button).

Clicking the button on a worksheet named "A", produces a sheet "Print" based on the active worksheet "A". The formula of the cells in the "Print" sheet reference "A" (e.g. 'A'!B1).

I have a worksheet named "B". When I click the button it should replace the "A" in the formula of all cells in "Print" worksheet.

It seems that wildcard in replace doesn't work.

For Each cell In ActiveSheet.Range("A1:C6")
    cell.Formula = Replace(cell.Formula, "'*'", "'" + shtName + "'")
Next

where shtName is the name of the ActiveSheet (A or B).

Upvotes: 0

Views: 2419

Answers (3)

user6432984
user6432984

Reputation:

Depending on the old worksheet name (shtName) the formula could reference could be either shtName! or 'shtName'!. Here I create a defined name to get the correct reference. Next Range().Replace is used to update all the cells in the target range.

enter image description here

Sub TestReplace()

    ReplaceSheetNameInFormula Range("A1:C6"), "A", "Print"
    ReplaceSheetNameInFormula Range("A1:C6"), "A-b", "Print"

End Sub


Sub ReplaceSheetNameInFormula(Target As Range, OldSheet As String, NewSheet As String)
    Const TempName = "TempDefinedName"

    Dim What As String, Replacement As String
    Replacement = "'" & NewSheet & "'!"

    With Worksheets(NewSheet)
        .Names.Add TempName, Worksheets(OldSheet).Range("A1"), False
        What = Mid(.Names(TempName), 2, Len(.Names(TempName)) - 5)
    End With

    Target.Replace What, Replacement

End Sub

Upvotes: 0

Rav
Rav

Reputation: 1387

After multiple 5 pages searches in Google, it seems that the "Replace" function doesn't take wildcards very well. Instead, I used the "Replace" method.

 For Each cell In ActiveSheet.Range("A1:C6")

cell.Replace What:="'" + "*" + "'", Replacement:="'" + shtName + "'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, 
 SearchFormat:=False, ReplaceFormat:=False    

Next

Upvotes: 1

Mak
Mak

Reputation: 154

For Each cell In ActiveSheet.Range("A1:C6")
    cell.Formula = "'" & shtName & "'" & Right(cell.Formula, _ 
                           len(cell.Formula)-instrrev(cell.Formula, "'"))
Next

Upvotes: 0

Related Questions