Creating VBA code to reference cell text and create a dynamic Find & Replace

YASPLS

I work with multiple excel spreadsheets that require monthly updates for month end financial closing. I need to change references to outside workbooks so that it pulls in data from the current months file.

For example changing

"C:\Desktop\Folder\June"

to

"C:\Desktop\Folder\July"

I want to be able to enter the two months into specific cells in the worksheet and then run a VBA script to auto update the references throughout the workbook. I can record a "macro" for the Find & Replace function, but I can't figure out how to point the "Find" and "Replace" variables to the specific cells where I store the month values.

Here is the current code I have for conducting find and replace:

ActiveCell.Replace What:="Jun", Replacement:="Jul", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
DiegoAndresJAY

Replace the literal text strings of "Jun" and "Jul" with a cell reference. For this, you can use Range("A1"). Of course, you would put in the actual range.

ActiveCell.Replace What:=Range("A1"), Replacement:=Range("B1"),LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False`

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

HotTag

Archive