Changing Code within A Macro

AlanS

New Member
Joined
Jul 27, 2007
Messages
5
Is there a way to insert code into a macro that changes other code within the same macro?

I have a monthly macro I run where I need to change the month, i.e., in the macro it's in "XX/07" format. I need to change the XX to the current month, e.g., to 07/07.

Can this be done?

Thanks in advance for any help/insight.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Welcome to the board!


If you post your actual code I or someone else could show you what needs to be setup to adjust a variable dependant on the current month.
 

AlanS

New Member
Joined
Jul 27, 2007
Messages
5
Thanks for the quick reply.

The macro is quite long, so I just included one line where the XX/07 appears. Currently I'm just stepping into the macro and using the find and replace all function. I'm just to see if there is a way to automate the month change like with an input/dialog box that opens up while the macro is running. There are 40 XX changes that occur in the macro.

ActiveCell.FormulaR1C1 = _
"=IF('Accrual & Reversal Calculations'!R[11]C[2]<0,""REV XX/07 TAXES IN EXCESS OF PLAN"",IF('Accrual & Reversal Calculations'!R[11]C[2]>0,""ACCRUE XX/07 PROPERTY TAXES"",""""))"
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,095
Put this at the beginning of your routine
Code:
Dim monthString As String
monthString = Format(Today,"mm/yy")

and then later

Code:
ActiveCell.FormulaR1C1 = _ 
"=IF('Accrual & Reversal Calculations'!R[11]C[2]<0,""REV " & monthString &  " TAXES IN EXCESS OF PLAN"",IF('Accrual & Reversal Calculations'!R[11]C[2]>0,""ACCRUE " & monthString & " PROPERTY TAXES"",""""))"
 

AlanS

New Member
Joined
Jul 27, 2007
Messages
5
Thank you for your reply.

I input the code that you provided. The macro runs ok, but I get this result:

ACCRUE PROPERTY TAXES
ACCRUE PROPERTY TAXES

with no date between the text, in this case between the words Accrue and Property.

this is how the code provided appears in my macro:

Dim monthString As String
monthString = Format(Today, "mm/yy")

ActiveCell.FormulaR1C1 = _
"=IF('Accrual & Reversal Calculations'!R[11]C[2]<0,""REV " & monthString & " TAXES IN EXCESS OF PLAN"",IF('Accrual & Reversal Calculations'!R[11]C[2]>0,""ACCRUE " & monthString & " PROPERTY TAXES"",""""))"

Maybe a second pair of eyes can spot my error.

Thanks again for the help provided.
 

AlanS

New Member
Joined
Jul 27, 2007
Messages
5
I got it to work.

Needed to change Today to Date, and then it ran just great.

Thanks to everyone for their help. I'll more than likely be visiting this site a lot more often.

Take Care everyone.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,527
Members
425,480
Latest member
br400821

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