Changing Code within A Macro

[AlanS]

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.

 

[Datsmart]

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]

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]

Put this at the beginning of your routine

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

and then later

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]

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]

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.