replace value not same format as found value

[marimar02]

Hello,

I'm having trouble with a "find & replace" formula in VBA. Namely, I have 2 columns, Column "BH" where it will find current value and Column "BI" where I noted what the found value should be replaced to. However, both are 4 digit numbers and start with a 0. So, I'd like to replace "0001" with "0002", but "0001" replaces with a "2" omitting zeroes. Zeroes must be included in the replacement value.

Here is the code:

Private Sub Reclass_Accounts()    
Dim fndvlue As Variant
Dim rplcvlue As Variant
Dim x As Long
    
    fndvlue = Array(Sheets("Settings").Range("BH2").Value, Sheets("Settings").Range("BH3").Value, _
        Sheets("Settings").Range("BH4").Value, Sheets("Settings").Range("BH5").Value)


    rplcvlue = Array(Sheets("Settings").Range("BI2").Value, Sheets("Settings").Range("BI3").Value, _
        Sheets("Settings").Range("BI4").Value, Sheets("Settings").Range("BI5").Value)
    
'Loop through each item in Array
    For x = LBound(fndvlue) To UBound(fndvlue)
    
'Reclass Actuals
        Range("E3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Replace What:=fndvlue(x), Replacement:=rplcvlue(x), LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
            ReplaceFormat:=True
    
    Next x


End Sub

Thanks much...

 

[Yongle]

A couple of options for you

numbers

Insert this line above Next x

Selection.NumberFormat = "0000"

text

Amend this line

Selection.Replace What:=fndvlue(x), Replacement:=[COLOR=#ff0000]"'" & Format(rplcvlue(x), "0000")[/COLOR], LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
            ReplaceFormat:=True

 

[marimar02]

Awesome. Thank you for both options. I was sort of thinking the same about the numbers format (change all at once after the replacement) but it was the "text" version you sent I was looking for.

Thanks much...

 

[Yongle]

thanks for the feedback

 

[marimar02]

I have a follow-up question on this "text" version. If the line already has "'" before the number, this code doubles it so now it looks like this: ''0000 instead of '0000. How can I fix that?

A couple of options for you

numbers

Insert this line above Next x

Selection.NumberFormat = "0000"

text

Amend this line

Selection.Replace What:=fndvlue(x), Replacement:=[COLOR=#ff0000]"'" & Format(rplcvlue(x), "0000")[/COLOR], LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
            ReplaceFormat:=True

 

[Yongle]

After this line ...

    Selection.Replace What:=fndvlue(x), Replacement:="'" & Format(rplcvlue(x), "0000"), LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

Insert ...

    Selection.Replace What:=Chr(39), Replacement:="", LookAt:=xlPart

You might expect the code to remove EVERY apostrophe but the leading apostrophe remains

Explanation
A leading apostrophe is a formatting character ( it is NOT part of the cell's content )
- therefore Find and Replace cannot be used to remove a leading apostrophe
- a leading apostrophe is ignored
- the 2nd apostrophe is part of the cell's content and is replaced

 

[marimar02]

Thank you @Yongle and thanks for taking the time to explain. No wonder I couldn't find it with "LEFT" function. This explains it perfectly.

You guys are amazing!