Creating Find and Replace Macro Utilizing Input Box Value

chuckdanger

New Member
Joined
Apr 27, 2015
Messages
7
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 . . .


Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Apr 27, 2015
Messages
7
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Apr 27, 2015
Messages
7
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

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

will activate the main financial shell. . .

Code:
[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)

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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
Top