Basic question about inserting a date into a formila

I know this is a pretty basic question but I'm new to excel so any help would be appreciated. I am creating a macro that will take the place of a repetitive sequence that I have been doing manually (the purpose of most macros, right?). At one point I am adding the following formula into a cell:

=ROWS(B2:B145)&" Badges received January 25, 2013"

I have been entering the date manually thus far but would prefer the macro to use a formula to enter the date. Ideally it would be the date of the next day after the macro was run, but the date the macro was run would work.

Alternatively, if the macro could stop and wait for me to input the information and then continue to run I could continue to type the date manually and then instruct the macro to continue.

Any suggestions?

Thanks,
David

Try

=ROWS(B2:B145)&" Badges received " & Format(Date+1,"mmmm dd, yyyy")

That's gets me an error message. I've attached a sample to demonstrate. Am I doing something wrong?

David

Well, you said you were creating a Macro to put the formula in the cell, so I used VBA Terms (format and date)
So it would be like

Range("A1").Formula = "=ROWS(B2:B145)&" Badges received " & Format(Date+1,"mmmm dd, yyyy")"

But to enter it directly into a cell by hand it would be
=ROWS(B2:B145)&" Badges received " &TEXT(TODAY()+1,"mmmm dd, yyyy")

In VBA it's Format and Date
In formula it's Text and TODAY()

Originally Posted by Jonmo1

Try

=ROWS(B2:B145)&" Badges received " & Format(Date+1,"mmmm dd, yyyy")

I'm still getting an error message when I run tht in a macro. I've attached another sample. Thanks for all your help!

The formula you gave me to run NOT in a macro worked. I want the cell contents to show the number of cells in column B that contain data and then the text "Badges received" and then show the date for the day after the information is entered.

Does that make sense?

So this is how you want the formula to look in the cell
=ROWS(B2:B145)&" Badges received " &TEXT(TODAY()+1,"mmmm dd, yyyy")

Then
ActiveCell.Formula = "=ROWS(B2:B145)&"" Badges received "" &TEXT(TODAY()+1,""mmmm dd, yyyy"")"

That worked, you guys are great. How can I change the range from a fixed B2:B145 to B2:last cell in column B with data?

How about

ActiveCell.Formula = "=COUNTA(B:B)-1&"" Badges received "" &TEXT(TODAY()+1,""mmmm dd, yyyy"")"

The -1 is assuming B1 is NOT blank.

Perfect, thanks again!

Glad to help, thanks for the feedback.