VBA Multiplication Formula

[hobbiton73]

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

[snb]

If you make a table (Excel 2007, 2010,2013) of the data in each sheet you won't need this code.
Every time you enter somthing new under the last row of the table (VBA: Listobject) all formulae will be applied to the 'new' row.

[hobbiton73]

Hi @snb, thank you very much for taking the time to reply to my post.

Unfortunately, I'm using Excel 2003, which is why I'm unable to use the 'Table' function.

Many thanks and kind regards