Insert formula depending on cell values

[thedobbs]

Hi guys. Bit of a problem here. I have a "Resource Plan" in excel and rather than have 1000s of cells with formulas in I am trying to run a macro that inserts the formula only where it is needed. I am getting there but one line appears to be beating me. Can anybody help, or does anyone know a simple way of doing what I am trying to do.

Here is my formula below:

Sub TestSpread()
Dim RowCount As String
Dim ColCount As String
RowCount = ActiveCell.Row
ColCount = ActiveCell.Column
Dim c1 As Range
For Each c1 In Range("TestRange")
If Cells(RowCount, 16).Value > Cells(12, ColCount).Value Then
c1.FormulaR1C1 = "=IF(AND(RC16>=R12C,RC16<R12C+6),(NETWORKDAYS(RC16,R12C+5,Holidays)/5),IF(AND(RC16<=R12C,RC18>R12C+5),(NETWORKDAYS(R12C,R12C[1]-1,Holidays)/5),IF(AND(RC18>=R12C,R12C>RC16),(NETWORKDAYS(R12C,RC18,Holidays)/5),"""")))"
End If
Next c1
End Sub

This line appears to be the problem:
If Cells(RowCount, 16).Value > Cells(12, ColCount).Value Then

 

[VoG]

Try like this

Sub TestSpread()
Dim RowCount As Long
Dim ColCount As Long
Dim c1 As Range
For Each c1 In Range("TestRange")
    RowCount = c1.Row
    ColCount = c1.Column
    If Cells(RowCount, 16).Value > Cells(12, ColCount).Value Then
        c1.FormulaR1C1 = "=IF(AND(RC16>=R12C,RC16<=R12C,RC18>R12C+5),(NETWORKDAYS(R12C,R12C[1]-1,Holidays)/5),IF(AND(RC18>=R12C,R12C>RC16),(NETWORKDAYS(R12C,RC18,Holidays)/5),"""")))"
    End If
Next c1
End Sub

 

[thedobbs]

Thanks for that, that is brilliant.

How would I go about adding another if statement.
For example

If Cells(12, ColCount).Value >= Cells(RowCount, 16).Value Then
AND ALSO
If Cells(12, ColCount+1).Value >= Cells(RowCount, 18).Value Then

Also:
Can I do ColCount+1??