Automate formulated rows based on user input

**G2S** · 01-31-2012, 09:13 PM

I have seen many similar questions and a lot of great answers, so I am hoping someone can help me. I have no knowledge of macros...so please be kind.

I have a "cash burn" schedule that I have built and it works great, but I would like to automate it even more.

I would like to have it add/subtract rows with calculated formulas based on user input. At the top of the sheet, I input the "Total Cost" in F2 and the "Duration" in K2. Based on the Duration(K2) I would like the numbered rows beginning at Row 4 to expand and/or contract keeping the formulas and row numbers (+1). The formulas distribute cash over a given time period.

Here is the file:
Construction Spend.xls

Thanks in advance.

**JapanDave** · 01-31-2012, 10:20 PM

OK, try this,

Click the calculate button and enter the amount of rows you want to calculate, eneter the amount, enter the duration in the prompt boxes.

Please Login or Register  to view this content.

**G2S** · 02-01-2012, 10:37 AM

@JapanDave,

Thanks, it works. It did drop the currency formatting when I entered a duration of 36 on one of the expanded cells. Is there a way to have the calculate button default to the current value? For instance, if you want to run another scenario with a different dollar amount but keep the same duration or vice versa.

**JapanDave** · 02-01-2012, 06:02 PM

See if this does what you are after?

**G2S** · 02-01-2012, 06:19 PM

Not exactly. The calculate field comes up blank, and if you don't input anything, it zeros out the amounts in the corresponding cells or comes up with a run time error. If all else fails the first version works great except in office for Mac 2008...evidently VBA is not supported in that version.

If you have time and can get it working, it would be great, if not I am better off than I was.

**JapanDave** · 02-01-2012, 06:35 PM

I am not sure I follow. What do you mean by the calculate field? Are talking about the input box where you enter a value when prompted? What is sheet3 for? I am sure there is a solution, so don't worry.

**G2S** · 02-01-2012, 07:05 PM

Sheet 3 is a hangover, you can disregard. My apologies. To clarify, I was referring to the input field when you click on the calculate button. It would be great if this had a default value that was the last value entered so if you only wanted to change the duration and keep the cost as it is, you wouldn't have to enter the cost again. You could just click 'OK' when the cost input came up and the current value would remain.

Does this make sense?

**JapanDave** · 02-01-2012, 08:02 PM

OK, Give this a whirl.

**JieJenn** · 02-01-2012, 08:09 PM

define type as static if you want to preserve the value of the last user inputs

**JapanDave** · 02-01-2012, 10:06 PM

JieJenn, it does not have to have variable defined as a static variable. If the value was being cleared, then yes I would say use one, but in this case the value remains in the cell so referring to the cell achieves the same result. Just my thoughts on the example, I am open to other opinions however.