Solved Insert preformatted rows based on user input from cell

January 31, 2012 at 16:55:21
Specs: Windows XP
I have seen many similar questions and great answers, so I am hoping someone can help me. I have a cash burn schedule that I have built and 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 I input the Project Budget in F2 and the Duration in K2. Based on the Duration(K2) I would like the numbered rows beginning Row 4 to expand and contract with formulas and row numbers.

Here is the file:Construction Spend.xls


See More: Insert preformatted rows based on user input from cell

Report •


✔ Best Answer
February 1, 2012 at 15:01:14
Try this version.

If it gives you problems, please post the copy of the workbook you've tried it in, leaving the code in the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to K2
   If Target.Address = "$K$2" Then
'Find last Row with data in Column B
    lastRw = Range("B" & Rows.Count).End(xlUp).Row
'Delete B:E cells, leaving 3 months to use as AutoFill
     Range("B7:E" & lastRw).Delete shift:=xlUp
'Get number of months from K2
     numMonths = Range("K2")
'AutoFill Columns B:E based on value in K2
    Range("B4:E6").AutoFill Destination:=Range("B4:E" & numMonths + 3), _
        Type:=xlFillDefault
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 31, 2012 at 18:21:28
Is there a minimum number of months that will be entered in K2?

Do the formulas currently in Rows 35 and 37 need to be placed in the same relative positions if there were more (or less) rows?

Why does the formula in E35 only sum the values in E4:E27 when you have data in E4:E33?

=SUM(E4:E27)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 1, 2012 at 06:47:14
@DerbyDad03,

You could assign a minimum of 6 months. I think that would be about the shortest duration.

They could be moved to the side to get them out of the way. I just need some way of knowing if the standard deviation in I2 needs adjusting to get me close to the total cost.

E35 should have summed the entire column from E4 to E33...operator error.

Thanks for the quick reply, I appreciate it.


Report •

#3
February 1, 2012 at 07:54:15
I can not download your spreadsheet at work, so I'm going off of memory here.

This code will fire when you enter your Duration in K2. It will delete everything below C6:E6, leaving 3 months to use as the autofill criteria. It will then autofill Column C:E based on the Duration value in K2.

It does not do anything about writing the other formulas that are currently in Rows 35 and 37 since, frankly, I don't remember what they are.

If you'll tell me what those formulas are and where you want them placed, I can see about modifying the code to have them end up where you want.

Another option is to place them at the top of sheet, outside of Column C:E so that they are not impacted by the deletion and additon of the Rows.

If you used a Named range for e.g. Column E, you could use somthing like
=SUM(myColumnE) to sum all the values in Column E regardless of how many Rows contained data.

I suggest you try this in a backup copy of your workbook since macros cannot be undone.

1 - Right click the sheet tab for the sheet you are working with.

2 - Paste this code into the pane that opens.

3 - Enter a number in K2

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to K2
   If Target.Address = "$K$2" Then
'Find last Row with data in Column B
    lastRw = Range("C" & Rows.Count).End(xlUp).Row
'Delete B:D cells, leaving 3 months to use as AutoFill
     Range("C7:E" & lastRw).Delete shift:=xlUp
'get number of months from K2
     numMonths = Range("K2")
'AutoFill Columns B:D based on value in K2
    Range("C4:E6").AutoFill Destination:=Range("C4:E" & numMonths + 3), _
        Type:=xlFillDefault
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 1, 2012 at 10:56:41
@DerbyDad03

Ok, I think it is close but there are a couple of issues:

I relocated my validation cells, when I input a new value in K2 my validation cell does not sum all the values in Column D as it had before.

Column B also needs to auto adjust, so the month numbers coincide with the rows of data.


Report •

#5
February 1, 2012 at 10:59:53
Also, when I enter a longer duration Column B does not auto populate with the new months. and the same issue with the validation cells.

Report •

#6
February 1, 2012 at 11:57:02
Like I said, I can't open your spreadsheet at work so I didn't remember which columns you were using. I can look at it when I get home if you haven't fixed it by then.

If you are going to use the code, you should at least understand it a little bit.

Perhaps if you looked at the code and the green comments, you might be able to edit the Column letters to match the columns you are working with. e.g where I have used C and E, change them to B and D if B:D are the columns in use.

As far as summing an entire column, did you see my comment about using a Named Range?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 1, 2012 at 12:10:28
@DerbyDad03

I couldn't agree more about understanding the code. Unfortunately, I tried replacing 'C' with 'B' and really screwed things up. I appreciate your help with this and it is not urgent.

I have adjust the sum to add the entire column, which works.

Thanks again.


Report •

#8
February 1, 2012 at 15:01:14
✔ Best Answer
Try this version.

If it gives you problems, please post the copy of the workbook you've tried it in, leaving the code in the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to K2
   If Target.Address = "$K$2" Then
'Find last Row with data in Column B
    lastRw = Range("B" & Rows.Count).End(xlUp).Row
'Delete B:E cells, leaving 3 months to use as AutoFill
     Range("B7:E" & lastRw).Delete shift:=xlUp
'Get number of months from K2
     numMonths = Range("K2")
'AutoFill Columns B:E based on value in K2
    Range("B4:E6").AutoFill Destination:=Range("B4:E" & numMonths + 3), _
        Type:=xlFillDefault
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
February 1, 2012 at 16:25:24
Perfect! Exactly what I was looking for.

Report •

Ask Question