VBA Help Please- Calculations

[DesperateKid]

I need to calculate something called a certain dates using something called Forward Pass and Backward Pass Methods(see below). Using the table(found at the link at the bottom) as an example (however macro needs do calculations using the lastrow with data inputed for the range).

I need to have the following:

1 macro that calculates the Early Start (ES) and Early Finish (EF) dates for all activities using the Forward Pass method described below:

 ES is the earliest date by which an activity can start assuming all the preceding activities are completed as planned.
 Hence, by default, ESThe first activity = 1 (i.e. the first day of the project)  For other activities, ESActivity B = EFActivity A + 1

 EF is the earliest date by which an activity can be completed assuming all the preceding activities are completed as planned. Therefore,

EFActivity A = ESActivity A + DurationActivity A – 1

 When many activities lead into one activity in the Forward Pass method, chose the highest EF date to calculate the ES date for the succeeding activity
o 1 macro that calculates the Late Start (LS) and Late Finish (LF) dates for all activities using the Backward Pass method described below:

 LF is the latest date an activity can finish in order to meet the planned completion date.  Hence, by default, LFThe last activity = EFThe last activity (i.e. the last day of the project)  For other activities, LFActivity A = LSActivity B – 1

 LS is the latest date an activity can start in order to meet the planned completion date. Therefore,

LSActivity A = LFActivity A - DurationActivity A + 1

 When many activities lead into one activity in the Backward Pass method, chose the lowest LS date to calculate the LF date for the preceding activity.




This is the coding i initially had however i did not take the preceding activities into account.

Function ForwardPass()

Dim ESrng, EFrng As Range
Dim FirstES, ES, EF As Integer



FirstES = "1"
 Worksheets(1).Range("I3").Value = FirstES

firstEF = (FirstES + Worksheets(1).Range("c3").Value - 1)
Worksheets(1).Range("j3").Value = firstEF

ESrng = ("I3:I")
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown


Range("J3").Formula = "=$I3 + $C3 -1"
Range("J3", "J" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
 
End Function

Function BackwardPass()

Dim LastRow
Dim LastLF As Integer


Range("J" & Cells(Rows.Count, 1).End(xlUp).Row).Select
 Selection.Copy
 Range("J" & Rows.Count).End(xlUp).Offset(0, 2).Select
Selection.PasteSpecial




End Function

https://www.dropbox.com/s/na4gj8bsbx...%20AM.png?dl=0

Thanking you endlessly in Advanced.

 

[Worf]

Hi

I'll look into this as soon as possible...

 

[Worf]

Cross posted here: Forward Pass and Backward Pass calculations

DesperateKid, is your problem solved?

 

[DesperateKid]

Solved thanks