vba Remove text from Formula

[gtd526]

Hello,
I have this formula in several cells containing "*". I'd like to remove the "*" from the formulas.
The range of cells with "*" in the formula is Z2:CN4.
Thank you.

Here is a limited example:

Wagers.xlsm
	Y	Z	AA	AB	AC	AD
1	M-Q-R:V-X:Z-W (6)		M-Q-R:V-Y:Z-W (6)		M-R:V-X:Z-W (6)
2	W:	0	W:	0	W:	0
3	L:	0	L:	0	L:	0
4	Psh:	0	Psh:	0	Psh:	0
NBA

Cell Formulas
Range	Formula
Y1	Y1	="M-Q-R:V-X:Z-W"&" ("&RANK(Y9,$Y$9:$CN$9)&")"
AA1	AA1	="M-Q-R:V-Y:Z-W"&" ("&RANK(AA9,$Y$9:$CN$9)&")"
AC1	AC1	="M-R:V-X:Z-W"&" ("&RANK(AC9,$Y$9:$CN$9)&")"
Z2	Z2	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-X:Z-W*",$A$3:$A$1190,"W")+(Z4*0.5)
Z3	Z3	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-X:Z-W*",$A$3:$A$1190,"L")+(Z4*0.5)
Z4	Z4	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-X:Z-W*",$A$3:$A$1190,"psh")
AB2	AB2	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-Y:Z-W*",$A$3:$A$1190,"W")+(AB4*0.5)
AB3	AB3	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-Y:Z-W*",$A$3:$A$1190,"L")+(AB4*0.5)
AB4	AB4	=COUNTIFS($L$3:$L$1190,"*M-Q-R:V-Y:Z-W*",$A$3:$A$1190,"psh")
AD2	AD2	=COUNTIFS($L$3:$L$1190,"*M-R:V-X:Z-W*",$A$3:$A$1190,"W")+(AD4*0.5)
AD3	AD3	=COUNTIFS($L$3:$L$1190,"*M-R:V-X:Z-W*",$A$3:$A$1190,"L")+(AD4*0.5)
AD4	AD4	=COUNTIFS($L$3:$L$1190,"*M-R:V-X:Z-W*",$A$3:$A$1190,"psh")

 

[jsb1921]

modify this to your need. if it helps you pl mark it as a solution for the benefit of future readers of the thread.

Sub Macro1()
Dim range1 As Range
Set range1 = Range("Z2:CN4")
For Each C1 In range1.Cells
'    this will be easy for you to understand
'    str1 = C1.Formula
'    str1 = Replace(str1, "*", "")
'    C1.Formula = str1
     C1.Formula = Replace(C1.Formula, "*", "")
Next
End Sub

 

[gtd526]

modify this to your need. if it helps you pl mark it as a solution for the benefit of future readers of the thread.

Sub Macro1()
Dim range1 As Range
Set range1 = Range("Z2:CN4")
For Each C1 In range1.Cells
'    this will be easy for you to understand
'    str1 = C1.Formula
'    str1 = Replace(str1, "*", "")
'    C1.Formula = str1
     C1.Formula = Replace(C1.Formula, "*", "")
Next
End Sub

It worked, Thank you.
Your macro was changing (Z4*0.5) to (Z405), removing the *, as it should. So I used ctrl H to change them back, "40" to "4*". Done.
COUNTIFS($L$3:$L$1190,"M-Q-R:V-X:Z-W",$A$3:$A$1190,"W")+(Z4*0.5)

 

[Peter_SSs]

What about this?
I think it should avoid the problem you mentioned and it also does all the replacements at once rather than cell-by-cell.

Sub Replace_Ast()
    With Range("Z2:CN4")
      .Replace What:="""~*", Replacement:="""", LookAt:=xlPart
      .Replace What:="~*""", Replacement:="""", LookAt:=xlPart
    End With
End Sub

 

[gtd526]

What about this?
I think it should avoid the problem you mentioned and it also does all the replacements at once rather than cell-by-cell.

Sub Replace_Ast()
    With Range("Z2:CN4")
      .Replace What:="""~*", Replacement:="""", LookAt:=xlPart
      .Replace What:="~*""", Replacement:="""", LookAt:=xlPart
    End With
End Sub

I've tested and it works!
Thank you.

 

[Peter_SSs]

I've tested and it works!
Thank you.

You're welcome. Thanks for the follow-up.