Hi all, i got the code below and need some help to modify it to meet my
new requirements.
This code works great, i just need it to count if the range "AQ" is
either blank or "N", but cannot seem to get it right ??

Sub TotalNoPo()
'
Application.DisplayAlerts = False
Dim LastRowParts As Variant, LastRowSummary As Variant, NumberBlanks
As Variant
Dim RowCount As Long, PartID As Variant, C As Variant, SumRowCount
As Variant, PartRowCount As Variant
Dim Tdate As Date

Tdate = Date

With Sheets("ALL LC PARTS")
LastRowParts = .Cells(Rows.Count, "C").End(xlUp).Row
End With

With Sheets("0908 RMT")
LastRowSummary = .Cells(Rows.Count, "C").End(xlUp).Row
For SumRowCount = 20 To LastRowSummary
PartID = .Range("C" & SumRowCount)
If IsNumeric(PartID) Then
With Sheets("ALL LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("B" & PartRowCount) Then

*** I need here if "AQ" is empty or "N" it must count ****

If IsEmpty(.Cells(PartRowCount, "R")) And
IsEmpty(.Cells(PartRowCount, "L")) Then
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
Else
With Sheets("ALL LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("L" & PartRowCount) Then
If IsEmpty(.Cells(PartRowCount, "R")) Then '--- No PO
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
End If
.Range("J" & SumRowCount) = NumberBlanks


Next SumRowCount

MsgBox " DONE..."

End With

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

If IsEmpty(.Cells(PartRowCount, "AQ").Value) Or _
.Cells(PartRowCount, "AQ").Value = "N") Then


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Les Stout" wrote in message
...
Hi all, i got the code below and need some help to modify it to meet my
new requirements.
This code works great, i just need it to count if the range "AQ" is
either blank or "N", but cannot seem to get it right ??

Sub TotalNoPo()
'
Application.DisplayAlerts = False
Dim LastRowParts As Variant, LastRowSummary As Variant, NumberBlanks
As Variant
Dim RowCount As Long, PartID As Variant, C As Variant, SumRowCount
As Variant, PartRowCount As Variant
Dim Tdate As Date

Tdate = Date

With Sheets("ALL LC PARTS")
LastRowParts = .Cells(Rows.Count, "C").End(xlUp).Row
End With

With Sheets("0908 RMT")
LastRowSummary = .Cells(Rows.Count, "C").End(xlUp).Row
For SumRowCount = 20 To LastRowSummary
PartID = .Range("C" & SumRowCount)
If IsNumeric(PartID) Then
With Sheets("ALL LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("B" & PartRowCount) Then

*** I need here if "AQ" is empty or "N" it must count ****

If IsEmpty(.Cells(PartRowCount, "R")) And
IsEmpty(.Cells(PartRowCount, "L")) Then
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
Else
With Sheets("ALL LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("L" & PartRowCount) Then
If IsEmpty(.Cells(PartRowCount, "R")) Then '--- No PO
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
End If
.Range("J" & SumRowCount) = NumberBlanks


Next SumRowCount

MsgBox " DONE..."

End With

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks so much Bob.

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***