Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert a new row on the and continue the formule

Status
Not open for further replies.
GRIFFIJ

GRIFFIJ

Programmer
Aug 29, 2002
43
GB
Can anyone help me with this query in Excel 2003?

Background

I have a spreadsheet that keeps track of employee holidays, for example in column “A” I have list of Employee names in column “D” I have the number of holidays that that person is entitled to, Column “E” the number of Holidays taken which is subtracted from “D” and then a few other bits and pieces .

Column “N” I have the a date Sun 01/04/2006 that then follows on for 6 months to Sat 30/09/2006

And the underneath that column I have the exact setup with the send half of then year continue from the upper row.

I Have a couple of employees in already an to account for holidays an sick I use countif statements each employee as there own row with in both parts of the year one on top and on below for the second half of the year.

Example countif below For employee 1

=COUNTIF(N10:GM10,"s/2")/2+(COUNTIF(N31:GM31,"s/2")/2)+COUNTIF(N10:GM10,"s")+(COUNTIF(N31:GM31,"s"))

Example for Employee 2

=COUNTIF(N11:GM11,"s/2")/2+(COUNTIF(N32:GM32,"s/2")/2)+COUNTIF(N11:GM11,"s")+(COUNTIF(N32:GM32,"s"))

Question

Based on the above information

If I had ten Employees how would I write the VBA to insert a new row at
underneath the last one with “Name” as there name and follow the same countif
statement above but for that person

So for example it would be

=COUNTIF(N12:GM12,"s/2")/2+(COUNTIF(N33:GM33,"s/2")/2)+COUNTIF(N12:GM12,"s")+(COUNTIF(N33:GM33,"s"))

And this would continue ever time a pressed “New Person it would insert a line for them with there correct countif statements

Can it be done with Code on a command button if so can you please help
 
Sort by date Sort by votes


Hi,

Turn on your macro recorder and go thru the steps of inserting the rows you want and then copying the forumlas.

Turn off the macro recorder and observe the code. It will need to be modified to make it work in a program.

If you need help modifying this code, post back with your code.

Skip,

[glasses] [red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![tongue]
 
Upvote 0 Downvote
Dear Skip

Can I first of all thank you for your help with this matter. I have created a Macro that does what I want it to. Would it it be possible to help me with the VBA code.

Macro

Sub NewPerson()
'
' NewPerson Macro
' Macro recorded 15/01/2006 by Mr. Joel Griffith
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Rows("20:20").Select
Selection.Insert Shift:=xlDown
Rows("41:41").Select
Selection.RowHeight = 14.25
Rows("42:42").Select
Selection.Insert Shift:=xlDown
Range("D10").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "20"
Selection.AutoFill Destination:=Range("D10:D23"), Type:=xlFillDefault
Range("D10:D23").Select
Range("E10").Select
Selection.AutoFill Destination:=Range("E10:E23"), Type:=xlFillDefault
Range("E10:E23").Select
Range("F10").Select
Selection.AutoFill Destination:=Range("F10:F22"), Type:=xlFillDefault
Range("F10:F22").Select
Selection.AutoFill Destination:=Range("F10:F23"), Type:=xlFillDefault
Range("F10:F23").Select
Range("G10").Select
Selection.AutoFill Destination:=Range("G10:G23"), Type:=xlFillDefault
Range("G10:G23").Select
Range("J10").Select
Selection.AutoFill Destination:=Range("J10:J23"), Type:=xlFillDefault
Range("J10:J23").Select
Range("K10").Select
Selection.AutoFill Destination:=Range("K10:K23"), Type:=xlFillDefault
Range("K10:K23").Select
Range("L10").Select
Selection.AutoFill Destination:=Range("L10:L23"), Type:=xlFillDefault
Range("L10:L23").Select
Range("D32").Select
Selection.AutoFill Destination:=Range("D32:D45"), Type:=xlFillDefault
Range("D32:D45").Select
Range("E32").Select
Selection.AutoFill Destination:=Range("E32:E44"), Type:=xlFillDefault
Range("E32:E44").Select
Range("F32").Select
Selection.AutoFill Destination:=Range("F32:F45"), Type:=xlFillDefault
Range("F32:F45").Select
Range("E43").Select
Selection.AutoFill Destination:=Range("E43:E45"), Type:=xlFillDefault
Range("E43:E45").Select
Range("G32").Select
Selection.AutoFill Destination:=Range("G32:G45"), Type:=xlFillDefault
Range("G32:G45").Select
Range("J32").Select
Selection.AutoFill Destination:=Range("J32:J45"), Type:=xlFillDefault
Range("J32:J45").Select
Range("K32").Select
Selection.AutoFill Destination:=Range("K32:K45"), Type:=xlFillDefault
Range("K32:K45").Select
Range("L32").Select
Selection.AutoFill Destination:=Range("L32:L45"), Type:=xlFillDefault
Range("L32:L45").Select
Range("A20").Select
ActiveCell.FormulaR1C1 = "Name…"
Range("A32").Select
ActiveCell.FormulaR1C1 = "=R[-22]C"
Range("A32").Select
Selection.AutoFill Destination:=Range("A32:A44"), Type:=xlFillDefault
Range("A32:A44").Select
Range("D32:L45").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D10:L23").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D32:L45").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M48").Select
End Sub

Thanks again Joel
 
Upvote 0 Downvote


You might want to do this in more manageable chunks.

For instance in your macro there are inserts in Row 20 & Row 42 and then formatting you do in ranges D10:L23, D32:L45.

Is this ALL based on an initial selection of row 20?

Skip,

[glasses] [red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![tongue]
 
Upvote 0 Downvote
Dear Skip

Thanks for your help sorry about the overload I have broke it down into steps.

Step 1 - Insert Rows

Macro text

Sub NewEmployee()
'
' NewEmployee Macro
' Macro recorded 16/01/2006 by Mr. Joel Griffith
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Rows("21:21").Select
Selection.Insert Shift:=xlDown
Rows("43:43").Select
Selection.Insert Shift:=xlDown
End Sub

Can you help with the VBA to insert rows.

Joel with great thanks
 
Upvote 0 Downvote


If this is all based on the selection of a particular row AND is relative to that row, then...
Code: 
'insert rows base on current selection
  with selection
    .entirerow.insert shift:=xldown
    .offset(22).entirerow.insert shift:=xldown
  end with
so if you select row 50, you'ld get the same relative result.

Skip,

[glasses] [red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Question
reversed words in english leading new english words
Replies
10
Views
533
Chriss Miller
C
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
1K
remeng
remeng
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
589
strongm
strongm
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
953
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top