Macro to copy from one cell, find from another cell and find and replace within formulas of a range

[DocDeb]

I want to do a macro to use text that is referenced in a cell to find text in a range of formulas and use another cell to replace the found text in that range.
It's easy by hand, but tedious.

I have one cell that displays the text I want to find so I can replace it in the range, "=RIGHT(B3,6)"
I have another cell that displays the text I want to use to replace the found text in that range "=RIGHT(B7,6)"


So, in range B7:B21, find text of "=RIGHT(B3,6)" and Replace with text of "=RIGHT(B7,6)"

I just did a macro to paste the values of "=RIGHT(B3,6)" and "=RIGHT(B7,6)"
Then it would be, In range B7:B21, find text of "F8" and replace with text of "F9"

Thanks!

 

[JLGWhiz]

You could record a macro of your manual operation and work from that.

 

[DocDeb]

Thank you for your response!
Recording a macro of my manual operation will result in a Find/Replace function of the selection recorded.
I would like to do a Find/Replace within a range of formula cells, B7:B21 to search for various information.
So, I would like to copy the info needed from "=RIGHT(B3,6)" and replace it with info from "=RIGHT(B7,6)".
Any ideas?

 

[JLGWhiz]

I would like to copy the info needed from "=RIGHT(B3,6)" and replace it with info from "=RIGHT(B7,6)".

I am not sure what this means. Can you post a before and after scenario to help clarify what you are attempting. I unjderstand what the formulas do, just the copy and replace has me confused.

 

[DocDeb]

ADVERTISEMENT

I'm sorry, "=RIGHT(B3,6)" is in F5 and "=RIGHT(B7,6)" is in F6
There are 12 sheets of information.
The formulas in B7:B21 display various information from the 12 specific sheets, one sheet at a time.
"=COUNTIFS('US EU-Donate'!J:J,">="&$B$4,'US EU-Donate'!J:J,"<="&$B$5,'US EU-Donate'!C:C,"="&A9)"


The cell where the formula "=RIGHT(B3,6)" is (cell = F5), and displays the last 6 digits of a specific sheet name selected from a dropdown in B3.


Manually, I highlight "B7:B21".
Do "Ctl + h".
Copy the cell F5, "=RIGHT(B3,6)" and paste it into the Find and Replace window in the "Find what" field. Then copy cell F6, "=RIGHT(B7,6)" and paste it into the "Replace with" field.

 

[JLGWhiz]

Give this a try.

Sub t()
Dim fn As Variant, rp As Variant
    With ActiveSheet
        fn = .Range("F5").Value
        rp = .Range("F6").Value
        .Range("B7:B21").Replace fn, rp
    End With
End Sub

 

[DocDeb]

That works beautifully! Many thanks!!

 

[JLGWhiz]

That works beautifully! Many thanks!!

You're welcome,
regards, JLG