Hi All
I have 2 problems which I hope someone can assist with.
Background
I have a macro called Z_Consolidate_Monthly_Expenses that gets data from 4 or 5 sheets and puts the data in sheet called Monthly Cash Debits at the end of the month. (This works as desired). The data is inserted after from Row 3 onwards.
Cell A1 in sheet Monthly Cash Debits has the following formula:-
="Monthly Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy")
The macro then deletes sheet called Monthly Non Cash Debits and then copies Monthly Cash Debits and calls it Monthly Non Cash Debits.
As the sheet has been copied Cell A1 in sheet Monthly Non Cash Debits still has the following in it:-
Problem 1
How can I change the formula in sheet Monthly Non Cash Expenses A1 (as part of the macro) to read ="Monthly Non Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy").
I have tried to amend the macro with the following statement:-
and I keep getting a compiler error at the first & in the statement and I have no clue how to resolve it.
Problem 2
When data is being Consolidated from the 4 or 5 sheets, it is possible that there will no data that has been extrapolated as there is no data to extrapolate.
What I would like is if there is not data after ROW 3 (this implies there has been no data to extrapolate), then I would like Cell A6 to be set to “There is no Monthly Non Cash Debits for this month.”. Formatting should be Candara 22, Bold, and Colour set to Red. It should also be centred across columns A to I.
Any assistance offered for the problems will be appreciated.
I have 2 problems which I hope someone can assist with.
Background
I have a macro called Z_Consolidate_Monthly_Expenses that gets data from 4 or 5 sheets and puts the data in sheet called Monthly Cash Debits at the end of the month. (This works as desired). The data is inserted after from Row 3 onwards.
Cell A1 in sheet Monthly Cash Debits has the following formula:-
="Monthly Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy")
The macro then deletes sheet called Monthly Non Cash Debits and then copies Monthly Cash Debits and calls it Monthly Non Cash Debits.
As the sheet has been copied Cell A1 in sheet Monthly Non Cash Debits still has the following in it:-
Rich (BB code):
="Monthly Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy")
Problem 1
How can I change the formula in sheet Monthly Non Cash Expenses A1 (as part of the macro) to read ="Monthly Non Cash Expenses for "&" "&TEXT(Formula!E2,"MMMM yyyy").
I have tried to amend the macro with the following statement:-
Rich (BB code):
Range("A1").Value = "=Monthly NON Cash Expenses for " "&" "&TEXT(Formula!E2,"MMMM yyyy")
and I keep getting a compiler error at the first & in the statement and I have no clue how to resolve it.
Problem 2
When data is being Consolidated from the 4 or 5 sheets, it is possible that there will no data that has been extrapolated as there is no data to extrapolate.
What I would like is if there is not data after ROW 3 (this implies there has been no data to extrapolate), then I would like Cell A6 to be set to “There is no Monthly Non Cash Debits for this month.”. Formatting should be Candara 22, Bold, and Colour set to Red. It should also be centred across columns A to I.
Any assistance offered for the problems will be appreciated.