Replace part of a formula in Excel by using Macro

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
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. :p


Any help or assistance is greatly treasured.


Thanks. :)
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
Is a macro really necessary?
A simple "Find and Replace" should work, i.e. replacing "Jan" with "Feb".
 

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
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. :)
 
Last edited:

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
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? :confused:

Thanks. :)
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2008
Messages
571
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

New Member
Joined
Mar 5, 2012
Messages
16
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!!! :biggrin:

You guys are my hero!!!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,139
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top