Wildcards with Replace in VBA

[Opv]

Are wildcards permissable when employing the Replace function in VBA? Specifically, I have a formula which includes a year, month and day defined and I am attempting to make the formula dynamic by keying on a cell which change the Day value (as needed). Consequently, if the formula currently includes ...DAY(15).... and I changed the 15 for the current data month to whatever value is in Cell X (say, 20, for example), without changing all of the formulas for prior months. I set out to accomplish this by trying the Replace function in VBA. I've tested the following:

Sub testformula()


Dim rng1 As Range, rng2 As Range
Set rng1 = Range("Insert").Offset(-1, -1)
Set rng2 = Range("Insert").Offset(, -1)


rng1.Formula = Replace(rng1.Formula, "DAY(*)", "DAY(" & rng2.Value & ")")


End Sub

This code runs with no error but effects no changes to the current formula in rng1. Apparently, wildcards do not function the same way as they do in Instr, or am I missing something?

[p45cal]

try the range.replace method instead of the replace function (I don't think you can use wildcards in the replace functoin):

Sub testformula()
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("Insert").Offset(-1, -1)
Set rng2 = Range("Insert").Offset(, -1)
rng1.Replace What:="DAY(*)", Replacement:="DAY(" & rng2.Value & ")", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

[Opv]

That worked like a charm. Thanks.