hobbiton73
08-22-2013, 03:09 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to add a formula to the end of each row (column O) on given sheets named in the array.
Sub InsertTotals2()
Const StartRow As Long = 5
Dim LastRow As Long
Dim ws As Worksheet
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
If LastRow >= StartRow Then
ws.Range("O5:O" & LastRow - 3).FormulaR1C1 = "=Sum(RC3:RC14)/7.4"
End If
ws.Columns("B:O").AutoFit
Next ws
End Sub
If at all possible I'd now like to add this by adding another calculation to column P.
I'd like the new formula to look in column O of the same row and mutliply that figure by the number of working days in the current month.
To provide more detail I've included the layout of my sheet below.
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J
Column K
Column L
Column M
Column N
Column O
Column P (New Column)
Row 3
22
23
20
23
22
21
23
21
22
23
20
21
Row 4
Description
Apr-13
May-13
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13
Nov-13
Dec-13
Jan-14
Feb-14
Mar-14
Total
New Formula
Row 5
Test
2
2
3
7
So if we use the data in row 5 as an example.
I'd like the formula in column P to compare the current date to that shown in the headers in row 4.
When it finds a match, I'd like, if at all possible please, for the formula to then look at the corresponsing value in row 3.
Then take this figure and mutliply this figure by the figure in column O on the same row.
So using the above example, the result in column P would be 7 multiplied by 22, because we are currently in August 2013 equalling 154.
I can work out how to place the formula in the correct row, but I'm having some difficulty in actually coming up with the formula.
I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.
Many thanks and kind regards
I'm using the code below to add a formula to the end of each row (column O) on given sheets named in the array.
Sub InsertTotals2()
Const StartRow As Long = 5
Dim LastRow As Long
Dim ws As Worksheet
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
If LastRow >= StartRow Then
ws.Range("O5:O" & LastRow - 3).FormulaR1C1 = "=Sum(RC3:RC14)/7.4"
End If
ws.Columns("B:O").AutoFit
Next ws
End Sub
If at all possible I'd now like to add this by adding another calculation to column P.
I'd like the new formula to look in column O of the same row and mutliply that figure by the number of working days in the current month.
To provide more detail I've included the layout of my sheet below.
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J
Column K
Column L
Column M
Column N
Column O
Column P (New Column)
Row 3
22
23
20
23
22
21
23
21
22
23
20
21
Row 4
Description
Apr-13
May-13
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13
Nov-13
Dec-13
Jan-14
Feb-14
Mar-14
Total
New Formula
Row 5
Test
2
2
3
7
So if we use the data in row 5 as an example.
I'd like the formula in column P to compare the current date to that shown in the headers in row 4.
When it finds a match, I'd like, if at all possible please, for the formula to then look at the corresponsing value in row 3.
Then take this figure and mutliply this figure by the figure in column O on the same row.
So using the above example, the result in column P would be 7 multiplied by 22, because we are currently in August 2013 equalling 154.
I can work out how to place the formula in the correct row, but I'm having some difficulty in actually coming up with the formula.
I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.
Many thanks and kind regards