Combine Text and Calculated Results in a single cell

[tlc_in_OK]

I'm trying to combine text with the results of a calculation in a single cell via a macro, and haven't had much luck.

What I need it to look like is "Actual June Data as of 7/05/2011" across merged cells G36:J36. The words "Actual" and "Data as of" are text. The month is derived from another worksheet where the month is indicated by just an integer in a cell, i.e. 6, 8, etc. I've been trying to use "=Choose" with the months to write out the month name, but haven't been able to get that to work like it would by putting it in a cell as a formula. The "7/05/2011" is simply the current date.

I need this to be in a macro because users will be altering data, and I have to keep some statistics of the data before any changes are made.

Any ideas?

 

[Andrew Poulsom]

Example, using the month number in A1:

="Actual "&CHOOSE(A1,"January","February","March","April","May","June","July","August","September","October","November","December")&" Data as of "&TEXT(TODAY(),"mm/dd/yyyy")

 

[tlc_in_OK]

Thanks Andrew, but I keep getting a "sub or function not defined" error on the "Today()" function. . .I thought that was a standard Excel function. Also, since my cell with the month number is on another worksheet, will simply putting in the referenece, i.e Worksheet2!A2 work?

 

[Andrew Poulsom]

How can you get a "sub or function not defined" error when you enter a formula on a worksheet? TODAY is an in-built Excel function.

 

[tlc_in_OK]

That's just it. . .I'm not trying to do this as a formula in a worksheet cell, I'm trying to put the resulting value in the cell via a macro. I'm getting the error when I try to compile.

 

[Andrew Poulsom]

Well Date is the VBA equivalent of TODAY. And Format is the equivalent of TEXT.

 

[tlc_in_OK]

Perfect. . .that's what I needed! Thank you!