Formula to VB

[Rocky0201]

I have the following formula:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=IF(NETWORKDAYS(NOW(),G110)<1,1,IF(NETWORKDAYS(NOW(),G110)<=3,2,""))
<o> </o>
I need this in vb form where if the result of the first test is <1, I will add to a counter else if the result of the second test is <=3 I would add to another counter.
<o> </o>
Any help would be great….
<o> </o>
Many Thanks!!

 

[jbeaucaire]

Something like this, maybe:

Sub test()
Dim Cnt1 As Long, Cnt2 As Long

    Select Case Evaluate("NETWORKDAYS(Now(),G110)")
        Case Is < 1
            Cnt1 = Cnt1 + 1
        Case Is <= 3
            Cnt2 = Cnt2 + 1
    End Select

End Sub

The Cnt1 and Cnt2 could be replaced with a counter cell.

 

[Rocky0201]

Thank you very much, jbeaucaire!!!!

 

[Rocky0201]

I'm getting a Type mismatch during execution. I beleive it's because I am putting the value in a cell (cells(i,....) instead of the address of the cell.

Please let me know how I can change the statement to obtain my results.

Select Case Evaluate("NETWORKDAYS(Now(),Cells(i, RRptHD(RRptHDC7)))")
Case Is < 1
OIRGroupCount(2) = OIRGroupCount(2) + 1
Case Is <= Sheets("Lists").Cells(RptDueDate(RptDueDateR1), RptDueDate(RptDueDateC1))
OIRGroupCount(1) = OIRGroupCount(1) + 1
End Select

note: the i is incremented in a do loop.

Thanks....

 

[jbeaucaire]

The Evaluate() code I gave you represents an actual cell-type formula, not a vba-type formula. If you want to slip VBA references into it, you'll have to do it in a way that converts the results back to cell-type formula syntax.

I can't test this for you, but maybe something like:

Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")")

 

[Rocky0201]

Thanks JB...

I still get the type mismatch on this code (first line)...

If Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")") < 1 Then
OIRGroupCount(2) = OIRGroupCount(2) + 1
Else
If Evaluate("NETWORKDAYS(Now()," & Cells(i, RRptHD(RRptHDC7)) & ")") <= Sheets("Lists").Cells(RptDueDate(RptDueDateR1), RptDueDate(RptDueDateC1)) Then
OIRGroupCount(1) = OIRGroupCount(1) + 1
End If
End If

I'll have to revisit this in the am.... my laptop (Vista) is having all sorts of problems... I'll be on my office pc tomorrow...

Thanks again...