FTE Calculation

[Pinang]

Hi,
Can anybody help me out with user defined formula for FTE calculation?
I am looking for user defined VBA formula for yellow highlighted formula in attached file, refer attachment for more details.
Thanks in advance.
Pinang

 

[chirayu]

Assuming that "Percentage allocation" is always column D & "No of Days" is always in Row 3. Select the first yellow cell in desired column & it will fill the rest.

Sub Macro1()

Dim ColumnA As String
Dim ColumnB As String
Dim ColumnC As String
Dim ColumnD As String

ColumnA = Mid(ActiveCell.Offset(0, -1).Address, 2, 1)
ColumnB = Mid(ActiveCell.Offset(0, -2).Address, 2, 1)
ColumnC = Mid(ActiveCell.Address, 2, 1)
ColumnD = "D"

ActiveCell.Formula = "=IFERROR((" & ColumnA & ActiveCell.Row & _
"-" & ColumnB & ActiveCell.Row & _
"+1)/" & ColumnC & "$3*$" & ColumnD & ActiveCell.Row & "%,0)"

Range(ColumnC & "5:" & ColumnC & Cells(Rows.Count, ColumnB).End(xlUp).Row).Select
Selection.FillDown

End Sub

 

[Pinang]

Thanks Chirayu,

I am loking for User Defind Formula for the same.

 

[chirayu]

What do you mean by user defined?

 

[Pinang]

I mean to say User Defined Function. VBA code like below.

Function FTE(Resource_SD as Date, Resource_ED as Date....)
Satatement....
End Function