PDA

View Full Version : [SOLVED] Network days in VBA Calculation



hermones
03-30-2015, 08:09 AM
Hi All.
I have the below coding which has been causing me bother for 3 days now.
I am using Microsoft Excel 2010.

The code below should start on E1, go to the next empty cell, check the cell on the left if the right part of the value is L001 then add 0.5 into the current cell or else
check the cell on the left if the right part of the value is L002 then add the number of network days between the date in B2 and C2.
The code works fine when I run it the first time, then anytime after its adding a value but its not the network days calculation.
I guess this maybe because I have set the cell to B2:C2 for the network days but it will be B2:C3 for the first row, but then move down each time to B3:C3, then B4:C4 and so on.
Can anyone help?



range("E1").Select
If ActiveCell.Offset(1, 0).Value = "" Then
ActiveCell.Offset(1, 0).Select
Else
range("E1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

If ActiveCell.Value = "" Then
If Right(range("D2"), 4) = "*L001*" Then
ActiveCell.Value = Application.WorksheetFunction.networkdays(range("B2"), range("C2")) - 0.5
Else
If Right(range("D2"), 4) = "*L002*" Then
ActiveCell.Value = Application.WorksheetFunction.networkdays(range("B2"), range("C2"))
End If
End If
End If