[RESOLVED] Calculating Networkdays with a condition

[dwhite30518]

I was given the following code to automate entering the networkdays formula is a monthly report I have to do. I have attached a copy of the workbook I am working with and the code I currently have is...

Sub Networkdays()
    Dim wks         As Worksheet
    Dim cell        As Range

    Set wks = Worksheets("SLA Calculations")

    With wks
        If .Range("N1").Value = "Stratix Diagnostics TAT" Then
            For Each cell In .Range("N2", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "N"))
                If IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(RC[-3], RC[-2])"
                End If
                If Not IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(RC[-3], RC[-2])"
                End If
            Next cell
        End If
    End With
    With wks
        If .Range("T1").Value = "Moto TAT" Then
            For Each cell In .Range("T2", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "T"))
                If IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(RC[-5], max(RC[-2],RC[-4]))"
                End If
                If Not IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(RC[-5], max(RC[-2],RC[-4]))"
                End If
            Next cell
        End If
    End With
    With wks
        If .Range("W1").Value = "Stratix Repair TAT" Then
            For Each cell In .Range("W2", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "W"))
                If IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(max(RC[-5],RC[-7]), RC[-1])"
                End If
                If Not IsEmpty(cell.Value2) Then
                cell.FormulaR1C1 = "=NETWORKDAYS(max(RC[-5],RC[-7]), RC[-1])"
                End If
            Next cell
        End If
    End With
End Sub

The problem I have is that I would like to alter this formula with a condition. That condition being if the device has repeat repair data then subtract 4 from the networkdays formula and if no data in repeat repairs then subtract 2.

Ex.
Device A goes to vendor for repair, vendor returns after repaired, then device has to go back to vendor again and then vendor returns device, the formula I have been manually entering would be...

=Networkdays(O2,R2)-4

Then Device B goes to vendor and is returned from vendor and then returned to the customer and the formula I entered for this is...

=Networkdays(O2,P2)-2

So how do I alter the above code to reflect these conditions?


Thanks!!!!

[Siddharth Rout]

Sorry but how would I know if "the device has repeat repair data "?

[dwhite30518]

When you open the attached workbook you will see two records. One is a repeat repair and the other is a normal repair. Repeat repairs will have dates in columns "Q" and "R". The headers say repeat repair to vendor and repeat repair from vendor.

[Siddharth Rout]

So if there is data in R then you want this formula?

=Networkdays(O2,R2)-4

[dwhite30518]

Correct...ans if not then the formula should be...

=networkdays(O,P)-2

[Siddharth Rout]

The code is ready. Quick question. will the value of N2 become -2 and N3 become 1 after the formula is inserted?

[dwhite30518]

Only column T should have this condition when entering the formula. Column N should have the following formula...
=Networkdays(K2,L2)
And then Column W would have...
=Networkdays(Max(P2,R2),V2)

[Siddharth Rout]

I think, I may be slightly confused...

Which column are you putting this formula in?

=networkdays(O,P)-2

or

=networkdays(O,P)-4

[dwhite30518]

Those formulas should go in column T. The other 2 columns that have similar formulas do not need to change fr the code i posted previously.

[Siddharth Rout]

Is this what you are trying?

Sub Networkdays()
    Dim wks  As Worksheet
    Dim cell As Range
    Dim lRow As Long, r As Long
    
    Set wks = Worksheets("SLA Calculations")

    With wks
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        .Range("T2:T" & lRow).Formula = "=IF(R2="""",NETWORKDAYS(K2, L2)-2,NETWORKDAYS(K2, L2)-4)"
    End With
    
    '
    '~~> Rest of the code
    '
End Sub

[dwhite30518]

That's close enough...I can change a few of the columns and get it to work.

Thanks!!!!