Text in Macro

Lucy - Aug 16, 2012 at 08:41 AM
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012 - Sep 5, 2012 at 08:36 AM

I'm new to writing macros and wonder if anyone can help me with a couple of areas I'm stumbling on?

Firstly, let me say that I am writing this macro for other people to use so I'm doing my best to make it easy to access.

1. I want to add some text to a cell based on some input from the user.
How do I get a box to come up saying "Which month?";
And then use their reply to fill in the box with month total?
(ie. if they say January, I want the cell to say 'January Total')
2. I'm trying to get the macro to add some subtotals to my data.
I'd like a box to come up again saying "Number of first row?";
And then put a sum in the active cell to add from the given row down to the row above the active cell.
So far I've got:
Dim x As Integer
x = InputBox("Number of first row?")
ActiveCell.FormulaR1C1 = "=SUM(R(x)C:R[-1]C)"
but the sum isn't working properly.
3. Finally(!)
Can I embed an existing macro inside a new one without seeing all the lines?

Thanks you very much for any help or tips that anyone can give,


Hello Again,

I think perhaps I'm being too ambitious so am happy to leave points 1 and 3, but if anyone knows how I could get the subtotal to work, I would be really very grateful for your advice and time.

My input box gets the row number, but I can't work out how to define the start of the sum as the cell in row x of the active column.

Many Thanks to everyone for reading this,

3 responses


I have a problem which I think is like Lucy's. If anyone has the time to look at her problem, I wonder if you could look at mine too?

Every month I enter some transaction details on a spreadsheet (purchase/ sale qty/price, interest etc.) and it would be good to include a sum in the macro I run. At the moment the macro just formats the next row and then I have to put the sums in myself, but like Lucy, if there was a way to define the top row to sum from, I could get the totals in automatically.

Thanks a lot for your help, for me and Lucy!
Hi Beth,

It does sound like we're trying to do the same type of thing.
I'm tring a new approach (but it still doesn't work) - can you, or anyone else, help with the sum arguments?

Set topCell = Application.InputBox(prompt:="Select a cell", Type:=8)
ActiveCell.Value = Application.WorksheetFunction.sum("topCell:(R[-1]C"))

Thanks everyone for your time again,
ExcelJosh Posts 12 Registration date Wednesday August 8, 2012 Status Contributor Last seen October 12, 2012
Sep 5, 2012 at 08:36 AM

I'm not sure if it was a typo just in the forum, or if you copied and pasted from your code, but

ActiveCell.Value = Application.WorksheetFunction.sum("topCell:(R[-1]C"))

should probably be

ActiveCell.Value = Application.WorksheetFunction.sum("topCell:(R[-1]C)")

Notice the quote marks moved.

If this doesn't work, could you post a sample spreadsheet of what you are attempting? I'm a little confused.

Thank you,