Hi, I wonder whether someone may be able to help me please.
I have the following sheet layout.
[TABLE="width: 1500"]
[tr][td][/td][td]COLUMN B
[/td][td]COLUMN C
[/td][td]COLUMN D
[/td][td]COLUMN E
[/td][td]COLUMN F
[/td][td]COLUMN G
[/td][td]COLUMN H
[/td][td]COLUMN I
[/td][td]COLUMN J
[/td][td]COLUMN K
[/td][td]COLUMN L
[/td][td]COLUMN M
[/td][td]COLUMN N
[/td][td]COLUMN O
[/td][td]COLUMN P
[/td][td]COLUMN Q
[/td][/tr][tr][td]ROW 3
[/td][td][/td][td]22
[/td][td]23
[/td][td]20
[/td][td]23
[/td][td]22
[/td][td]21
[/td][td]23
[/td][td]21
[/td][td]22
[/td][td]23
[/td][td]20
[/td][td]21
[/td][td][/td][td][/td][td][/td][/tr][tr][td]ROW 4
[/td][td]Description
[/td][td]Apr-13
[/td][td]May-13
[/td][td]Jun-13
[/td][td]Jul-13
[/td][td]Aug-13
[/td][td]Sep-13
[/td][td]Oct-13
[/td][td]Nov-13
[/td][td]Dec-13
[/td][td]Jan-14
[/td][td]Feb-14
[/td][td]Mar-14
[/td][td]Actual Hours Total
[/td][td]Mandays Total
[/td][td]FTE Total
[/td][/tr][tr][td]ROW 5
[/td][td]Project 1
[/td][td]1155.14
[/td][td]1463.71
[/td][td]1615.53
[/td][td]1333.76
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]232.01
[/td][td]32.05
[/td][td][/td][/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/TABLE]
What I'm trying to do, is add a formula via VBA in the "FTE" column (Column Q), which is as follows:
- Search row 5 in columns C:N and find the month which matches the current date.
- When a match is found, take the corresponding value from row 4 and multiply this value by the value in column P.
- So using the above as an example, the formula in column Q on row 5, would be 22 (Because we are in August) multiplied by 32.05 (Cell P5) giving a total of 705.10.
I know how to set up my sheet and range array using the code I've put together below, but it's the formula I'm having difficulty with.
Sub Extract()
Dim ws As Worksheet, LastRow As Long
Const StartRow As Long = 5
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("B5:B" & LastRow).NumberFormat = "@"
ws.Range("C5:P" & LastRow).NumberFormat = "0.00"
End If
ws.Columns("B:Q").AutoFit
Next ws
Application.ScreenUpdating = True
End Sub
Display More
I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.
Many thanks and kind regards