VBA Calculate Working Number of Days

[hobbiton73]

Hi, I wonder whether someone could help me please.

I'm using the following sheet layout to display staff resource detail

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

COLUMN Q

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

Actual Hours Total

Mandays Total

FTE Total

ROW 5

Project 1

1155.14

1463.71

1615.53

1333.76

232.01

32.05

NEW FORMULA

What I'm trying to do, is add a formula via VBA in the "FTE" column (Labelled as NEW FORMULA in the above table), which performs the following:

• Search row 4 in columns C:N and find the month which matches the current month.

• When a match is found, take the corresponding value from row 3 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've attached a file which perhaps illustrates this better than I can explain, and I've started the code in VB editor, but it's just the formula that I'm having difficulty in working out.

I just wondered whether someone could look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards