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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
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:-

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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,385
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    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.
VBA Code:
    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.
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
Joe4,

Sorry for the delay in responding to your solutions.

As expected, worked a treat, just what the doctor ordered. (y):)

Have learned a couple of things alsong the way.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,385
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,137,125
Messages
5,679,751
Members
419,855
Latest member
Eddier32

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