Replace part of a formula in Excel by using Macro

[Azrin]

Hello everyone.


Is anyone knows how to automatically replace part of a formula, which is a link on that formula?


Right now, I'm trying to create a macro that would enable me to replace a link on a formula.


The formula is shown below:
=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9


I would like to know whether it is possible to create a macro that will prompts user to input the first three letters of a particular month (eg., Jan, Feb, Mac...) and then automatically replace the formula in a range of cells acccording to that particular month.


For example, the original formula is:
=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Jan!C9


When the user inputs Feb inside the prompt box, all the cells that contain this formula will be replaced with this formula:
=[School_Maintenance_Resource_Allocations_Fullview.xlsm]Feb!C9


The worksheet that contains this formula is consisting of Column A until BZ and the numbers of rows are indeterminate. However, the first four rows are reserved for headers. Thus, the first cell is A5.


The first workbook is the source of the link and contains multiple worksheets according to months.


Currently, I tried to use the indirect method but my boss, the headmaster along with several colleagues found it quite difficult for them to use.


Any help or assistance is greatly treasured.


Thanks.

 

[Joe4]

Is a macro really necessary?
A simple "Find and Replace" should work, i.e. replacing "Jan" with "Feb".

 

[Azrin]

Hello Joe4.

Thanks for the input.

I have tried using the "Find & Replace" method, but it took such a long time to finish and on certain computers, the process just either hang or completely crashed.

This is probably because the worksheet that we're worked on is huge. It consisted of approximately 34 t0 47 columns and the rows sometimes can get up to 1200 rows.

Hence, using the "Find & Replace" method is not that convenient for us.

Right now, I'm trying to use the indirect method, but no matter what I did, error will be displayed.

Thus, I hope that if anyone out there know how to accomplish this task using macro, it will be such a valuable solution to me.

Thanks.

 

[Azrin]

Hello, everyone.

I stumbled into a similar problem in one of the old posts here at MrExcel's forum board.

It uses Indirect function to replace the linked cell reference formula.

http://www.mrexcel.com/forum/showthread.php?t=292503

I tried to apply the same solution but it only worked on a single cell.

The Indirect formula that I used is as below:

=INDIRECT("[GDBA_FY12_POR_Resource_Allocations_Fullview.xlsm]" & $A$1 & "!C9")

Where in cell A1, the name of month is being inserted. (eg., Jan, Feb, Mac,...)

This formula only worked on cell C9 and when I tried to drag the formula into another cell, (eg., C10, the formula remains the same, without incrementing the cell).

I've tried several solutions such as removing the quote around !C9 but an error is displayed.

Anyone have any idea to solve this problem?

Thanks.

 

[Joe4]

Where in cell A1, the name of month is being inserted. (eg., Jan, Feb, Mac,...)

This formula only worked on cell C9 and when I tried to drag the formula into another cell, (eg., C10, the formula remains the same, without incrementing the cell).

I've tried several solutions such as removing the quote around !C9 but an error is displayed.

So are you saying that you are placing the formula in cell C9 on your sheet, and want it to reference C9 in your other file/sheet, and when you copy it down to C10, your want the formula to look in C10 on the other file/sheet too?

If so, try this variation of your formula:
=INDIRECT("[GDBA_FY12_POR_Resource_Allocations_Fullview.xlsm]" & $A$1 & "!C" & ROW())

 

[Ragnar1211]

I cannot see why indirect would be faster than find and replace...

1. Turn automatic calculations off
2. Replace contents
3. Turn automatic calculations on

 

[Azrin]

Thanks Joe4 and Ragnar1211

Hello everyone.

Just back from vacation last night.

Thanks to both of you, Joe4 and Ragnar1211, I've tried both solutions and they're working so well.

Joe4. thanks for the code statement, it worked superfine.

Ragnar1211, yes, what you said is true. The find and replace method is more faster and less complex than the indirect method. I adopted the find and replace method in my worksheet instead of using indirect. Great tips!!!

I luv u guys so much!!!

You guys are my hero!!!