Creating Find and Replace Macro Utilizing Input Box Value

[chuckdanger]

Hello, I would like to create a macro to find and replace parts of my model with data that the user chooses with input boxes. i have used this method before and it works well, there is just a missing link between the Input Box and Replace function

Sorry if my formatting is not perfected this is my first post on this forum, please let me know what I can do better.

This is part of what I have so far . . .

Dim rff As Variant
 Dim rfr As Variant
 Dim pyf As Variant
 Dim pyr As Variant
 
 budprod.Activate
 Sheets("US.TOTL").Activate
 Range("BO3").Activate
 
 ''Set Current Year RF and Budget Variables
 
Set x1 = Application.InputBox("In Row 5 Select Month That Just Ended", "Select RF & BUD 'Find' Column", Type:=8)
          '///////// RF & BUD FIND VARIABLE \\\\\\\\\\\
            rff = x1.Column
            
Set x2 = Application.InputBox("In Row 5 Select Month That Has Just Started", "Select RF & BUD 'Replace' Column", Type:=8)
          '///////// RF & BUD REPLACE VARIABLE \\\\\\\\\\\
            rfr = x2.Column

model.Activate
Sheets("Data Summary").Activate

Range("RFData").Select
Selection.Replace What:=rff, Replacement:=rfr, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

The macro will run smoothly but the values are not found and replaced so I am thinking its is how the variables are set up, all input welcome

 

[onlyadrafter]

Hello,

your code is selecting the column numbers to replace and replaced by, not the actual cell value. Is this what you require?

Can you provide a sample of the data and the results you are expecting.

 

[chuckdanger]

That is my intention to may I would like to create a macro that uses column references to advance the forecasting model one month. The macro will run through a variety of budget scenarios, and reforecasts to update them all at once with the new month. Does this help explain my logic.

 

[onlyadrafter]

Hello,

Can you provide a sample of the data and the results you are expecting.

In particular, a before and after scenario, together with the data you select in the column.

 

[chuckdanger]

I am not sure what data you require, but I will try and explain a bit better.

I have 5 neighborhoods in a production model (Set as "model") that require a find and replace at the beginning of every month. These neighborhoods are direct links to our financial shells (which are very large, cumbersome files that don't provide the best transparency of data to not finance persons). Being just direct links (no equations) they should be relatively simple to find and replace. So this macro will open the budget financial shell (all shells have same column, row layout) and will have the user simply select the column of past month and then the next input box will ask which month they are transitioning to. another simple click

[COLOR=#333333] budprod.Activate[/COLOR] Sheets("US.TOTL").Activate [COLOR=#333333] Range("BO3").Activate [/COLOR]

will activate the main financial shell. . .

[COLOR=#333333]Set x1 = Application.InputBox("In Row 5 Select Month That Just Ended", "Select RF & BUD 'Find' Column", Type:=8)[/COLOR]   :[COLOR=#333333] [U]rff = x1.Column[/U] [/COLOR]

Will allow the user to select what month they are transitioning from (in the shell actual[past] data is colored red for visibility)

Set x2 = Application.InputBox("In Row 5 Select Month That Has Just Started", "Select RF & BUD 'Replace' Column", Type:=8): [U]rfr = x2.Column[/U]

Will allow the user to select what month they are transitioning to


Then the macro will go to each neighborhood, designated with a named range, and conduct a find and replace.

I have troubleshot it to the point where i think something is wrong the the underlined parts of code where the variables are not set to a correct type or something along those lines.

Here is the kicker, when I did not have the ".Column" in the second part, it conducted the Find and Replace correctly. Any help on what to designate the variables would be great. maybe the column needs a Absolute Column reference portion.

Thoughts? I can provide more detail if needed.