holmes1013
New Member
- Joined
- Nov 17, 2011
- Messages
- 2
Before we dive in, my knowledge of macros extends to the point where I can tweak already written codes to suit my needs. I've searched all over but this one is still throwing me for a loop (no pun intended).
I'm in Excel 2010, Win7.
I am trying to build a budget forecast based on revenue vs headcount. For each +/- $230k in revenue, that equates to +/- one employee.
Forecasted revenue numbers will be changing, which will then change the count of new temp employees needed for that month so the insert/delete needs to be flexible.
Range K14:V14 holds the incremental headcount per month needed. This range won't change.
Current employee data starts in A19 (A18 are headers), this won't change, and the end of this list is variable (I want the flexibility to add more FT employees and not mess up macro to adjust for temp help).
What I need the macro to do is:
1) Starting in A18, find first blank row searching downward. There is other data below where I want to start inserting so I can't search for blank from bottom of sheet.
2) Starting in that first blank row, based on Jan headcount value in K14, insert (shift rows downward) that many new rows. This January value will always be >=0.
3) For newly inserted rows, COPY THE FORMULA of row above for Ranges(B:E,G:V). The value in column A should always = "NEW". The value in column F should equal the first day of the month that we're looking at. In this case, F would = 1/1/2012.
4) Now look at Feb headcount value in L14.
5) If Feb value > 0, insert new rows. If Feb value < 0, delete that number of rows. If Feb=0, do nothing. For new inserts, again (and last time), Col F should = 2/1/2012.
*** If it's easier, any dates in ColF can reference the range K8:V8 for Jan-Dec date values.
6) Loop though above for rest of months through December.
THANK YOU IN ADVANCE FOR ANY HELP OR GUIDANCE!
I'm in Excel 2010, Win7.
I am trying to build a budget forecast based on revenue vs headcount. For each +/- $230k in revenue, that equates to +/- one employee.
Forecasted revenue numbers will be changing, which will then change the count of new temp employees needed for that month so the insert/delete needs to be flexible.
Range K14:V14 holds the incremental headcount per month needed. This range won't change.
Current employee data starts in A19 (A18 are headers), this won't change, and the end of this list is variable (I want the flexibility to add more FT employees and not mess up macro to adjust for temp help).
What I need the macro to do is:
1) Starting in A18, find first blank row searching downward. There is other data below where I want to start inserting so I can't search for blank from bottom of sheet.
2) Starting in that first blank row, based on Jan headcount value in K14, insert (shift rows downward) that many new rows. This January value will always be >=0.
3) For newly inserted rows, COPY THE FORMULA of row above for Ranges(B:E,G:V). The value in column A should always = "NEW". The value in column F should equal the first day of the month that we're looking at. In this case, F would = 1/1/2012.
4) Now look at Feb headcount value in L14.
5) If Feb value > 0, insert new rows. If Feb value < 0, delete that number of rows. If Feb=0, do nothing. For new inserts, again (and last time), Col F should = 2/1/2012.
*** If it's easier, any dates in ColF can reference the range K8:V8 for Jan-Dec date values.
6) Loop though above for rest of months through December.
THANK YOU IN ADVANCE FOR ANY HELP OR GUIDANCE!
.EntireRow.Insert Shift:=xlShiftDown<br> <SPAN style="color:#007F00">' For newly inserted rows, COPY THE FORMULA of row above for Ranges(B:E,G:V).</SPAN><br> rngA.Resize(, 22).AutoFill Destination:=rngA.Resize(n + 1, 22)<br> <SPAN style="color:#007F00">' The value in column A should always = "NEW".</SPAN><br> rngA.Offset(1).Resize
