VBA Find and Replace based on a specific cell

[evenvasveen]

Hi,

I want to use macros to find and replace a text based on a specific cell value.
For example: if the text is "001563 0105" and i want to use macros to find "001563" and replace it with "", so in the end it just show "0105".

If cell value A1 contain the text "001563", the codes find this in the text "001563 0105" and replace it with "0105".
If i change cell value A1 with for example "001560", the codes find this text and remove it.

 

[Joe4]

Welcome to the Board!

The issue with trying to use Find/Replace (either manually or by VBA) is that it will when you drop the first number it will convert the second portion to a number, and you will lose the leading zero (so it would show "105", not "0105"). So it might be better to loop through the range you want to do the replacements in.
Can you tell me what range this VBA code should be searching in to make these replacements?

 

[evenvasveen]

Okey, I see. Technically it doesn’t matter if I loose the leading zero. It will still work with or without the zero in front (the four numbers stands for a date, like in my example 0105 stands for May 1th).

My range of where the VBA code should be searching for is H7:H100.
Thank you!

 

[Joe4]

OK, then this should be fairly easy. Try this:

Sub MyReplaceMacro()

    Dim str As String
    
'   Get value to replace from cell A1 (make sure blank space at end)
    str = Trim(Range("A1")) & " "

'   Replace values in range
    Range("H7:H100").Replace What:=str, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
End Sub

 

[evenvasveen]

OK, then this should be fairly easy. Try this:

Sub MyReplaceMacro()

    Dim str As String
   
'   Get value to replace from cell A1 (make sure blank space at end)
    str = Trim(Range("A1")) & " "

'   Replace values in range
    Range("H7:H100").Replace What:=str, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
       
End Sub

Thank you so much! It worked perfectly.

 

[Joe4]

You are welcome!
Glad I was able to help.