Help with inserting text and TEXT field in a macro and ascertain if there is data on sheet or not.

[Kayslover]

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:-

="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:-

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.

 

[Joe4]

Problem 1:
When assigning a formula and not a value, you use .Formula or .FormulaR1C1 and not .Value.
Sometimes the quotes can be messy, as they are use as delimiters and text qualifiers. The easiest thing to do is to turn on the Macro Recorder and record yourself entering an example of the formula you want in any cell. Then you can just copy and paste the formula piece of that to your code.

So your code should look something like this:

    Range("A1").Formula = "=""Monthly NON Cash Expenses for ""&"" ""&TEXT(Formula!E2,""MMMM yyyy"")"

Problem 2:
See if something like this does what you want for the statement you want to put in cell A6.

    Dim lr As Long
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Check to see if no data after row 3
    If lr <= 3 Then Range("A6") = "There is no Monthly Non Cash Debits for this month."

As for the formatting, just turn on the Macro Recorder and record yourself applying the formatting you want, and then you should have the code that you need to do that.

 

[Kayslover]

Joe4,

Sorry for the delay in responding to your solutions.

As expected, worked a treat, just what the doctor ordered.

Have learned a couple of things alsong the way.

 

[Joe4]

You are welcome.