Home VBA Find and replace keeps changing formatting of cell contents starting with "$"

Questions

VBA Find and replace keeps changing formatting of cell contents starting with "$"

January 27, 2023

the below code grabs the value from A2 which starts with "$" and replaces all values on the sheet with an input box.

The only issue is after find and replace it changes cell A2 to currency format because it starts with a $ and removes the $. Is there any way to avoid this?

Private Sub CommandButton1_Click()
Dim strInput As String

strInput = InputBox("Enter replacement value", Default:="$")
If strInput <> "" Then
Range("A2").Select
    Cells.Replace What:=Range("A2"), Replacement:=strInput, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2
End If
End Sub

>Solution :

Try it with an apostrophe:

Private Sub CommandButton1_Click()
Dim strInput As String

strInput = InputBox("Enter replacement value", Default:="'$")
If strInput <> "" Then
    Cells.Replace What:=Range("A2"), Replacement:=strInput, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2
End If
End Sub

Also, the Range("A2").Select is not required