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

DocDeb

New Member
Joined
Jan 15, 2008
Messages
19
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You could record a macro of your manual operation and work from that.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Give this a try.

Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top