Change Formula in sheet by adding a new part

[Abdul]

I have many formulas in a sheet like :

=ROUND('Revenue'!D$45*VLOOKUP($C116,ItemSplit!$A$1:$B$50,2,FALSE),0)

If the the formula do not contain *Code!$C$47 (multiply by) at the end
of the formula i want to add it to all the formula in the sheet.

Can it be done using VBA or by find, replace function

Thanks

 

[Charabeuh]

Hello,

A beginning of solution using VBA:
The active sheet must be the sheet where the formula to be changed are.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit

Sub AddToFormula()
Dim xR As Range, xF

For Each xR In ActiveSheet.UsedRange
xF = UCase(xR.Formula)
If Not IsNull(xF) Then
If Not (Len(xF) = 0) Then
If InStr(xF, "*CODE!$C$47") = 0 Then
xR.Formula = xR.Formula & "*Code!$C$47"
End If
End If
End If
Next xR

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''







"Abdul" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...

 

[Charabeuh]

Sorry, I have forgotten the most important:
to find the right formula to complete:

Option Explicit

Sub AddToFormula()
Dim xR As Range, xF

For Each xR In ActiveSheet.UsedRange
xF = UCase(xR.Formula)
If Not IsNull(xF) Then
If Not (Len(xF) = 0) Then
If InStr(xF, "45*VLOOKUP($") > 0 Then
If InStr(xF, "*CODE!$C$47") = 0 Then
xR.Formula = xR.Formula & "*Code!$C$47"
End If
End If
End If
End If
Next xR

End Sub