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

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

CodePudding user response：

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

CodePudding user response：

adding a " ' " apostrophe mark to the cell value does not affected the find and replace value and will keep the formatting the same. Thank you @cameron Critchlow

https://www.mrexcel.com/board/threads/vba-code-for-find-and-replace-keeps-changing-format.1119356/