Help with VBA loop, lookup and calculate problem

[Les]

Hi all, i have the following scenario that i need to automate,

1. 2 sheets in one workbook(Report.xls), "Summary" & "LC PARTS"
2. Sheet "Summary" Column "C" has either 4 numbers or a 3 letter code,
starting from row 20 down.
3. Sheet "LC PARTS" has the Code in column "H".
4. Sheet "LC PARTS" has the Number in column "B".
5. Sheet "LC PARTS" has a number in column "N".

What i want to do is loop down column "C" in sheet "summary" & find the
first code/number and then look for the same value in sheet "LC Parts" column
"H"(code) and column "B"(Number) if it has found a number and then count the
number of blank cells in colummn "N". This total must then be place in the
same row on sheet "Summary" in column "J". It must loop down to the last
code/number on the sheet "Summary" as more can be added over time.

Thank you very much in advance for any help.

Les

 

[Joel]

what exactlly do you mean by "Number of blank cells in column N". Do you
mean the number of spaces in the cell? wouldn't the number of blank cells in
column N always be a constant?

 

[Les]

Hi Noel, i mean the number of empty cells, as these are parts without an
order number which would be in column "N"

Summary Sheet:

"B" "C"
Bonnet Hood metal-Laepple 4161
Bumper (Front)-Venture 5111
Bumper (Rear)-Venture 5112
Centre Console-D&B CCL
Front End Module-Faurecia FRT
IMC-SAS IMC
Rear Axle Assembly-LMSA ACH

"LC SHEET":

A B C D F G
H N
4047116 3241 LC 4 SUCTION LINE 01 ACH
4047117 3241 LC 4 SUCTION LINE E9X AFS 01 ACH
4051502 3332 LC V DRIVE FLANGE HUB HIGH 01 ACH 2890210

Hope this helps.

 

[Joel]

I did exactly what you requested. I think your instruction are missing some
requirements

Sub test()

With Sheets("LC PARTS")
LastRowParts = .Cells(Rows.Count, "C").End(xlUp).Row
NumberBlanks = 0
For RowCount = 1 To LastRowParts
If IsEmpty(.Cells(Rows.Count, "N")) Then
NumberBlanks = NumberBlanks + 1
End If
Next RowCount
End With

With Sheets("Summary")
LastRowSummary = .Cells(Rows.Count, "C").End(xlUp).Row
For RowCount = 20 To LastRowSummary
PartID = .Range("C" & RowCount)
If IsNumeric(PartID) Then
.Range("J" & RowCount) = NumberBlanks
With Sheets("LC PARTS")
Set c = Columns("B:B").Find(what:=PartID, _
LookIn:=xlValues)
If Not c Is Nothing Then
'enter more code here
End If
End With
Else
With Sheets("LC PARTS")
Set c = Columns("H:H").Find(what:=PartID, _
LookIn:=xlValues)
If Not c Is Nothing Then
'enter more code here
End If
End With
End If
Next RowCount

End With

End Sub

 

[Les]

Hi Joel, I have to first get the value in sheet "Summary" column "C", either
a number or a 3 letter code e.g. 5141 or "SIT"

With this value found, if it is a number then i must look up in the sheet
"LC PARTS", column "B" for numbers and Column "H" for code, then count the
empty cells in column "N" .

 

[Joel]

That is what the code does. what is the purpose of the lookup?????

 

[Les]

The lookup is so that it knows the number or code that i require the result
for.
e.g. for the code "SIT" i have 500 parts and need to see how many have
purchase orders. Therefore if i count the empty cells in "N" it will either
give me a zero result if all prchase orders are ther or it will tell me there
are 100 empty cells which will tell me that i am missing a 100 purchase
orders.

 

[Joel]

try this code

Sub test()

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

With Sheets("Summary")
LastRowSummary = .Cells(Rows.Count, "C").End(xlUp).Row
For SumRowCount = 20 To LastRowSummary
PartID = .Range("C" & SumRowCount)
If IsNumeric(PartID) Then
With Sheets("LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("B" & PartRowCount) Then
If IsEmpty(.Cells(PartRowCount, "N")) Then
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
Else
With Sheets("LC PARTS")
NumberBlanks = 0
For PartRowCount = 1 To LastRowParts
If PartID = .Range("H" & PartRowCount) Then
If IsEmpty(.Cells(PartRowCount, "N")) Then
NumberBlanks = NumberBlanks + 1
End If
End If
Next PartRowCount
End With
End If
.Range("J" & SumRowCount) = NumberBlanks

Next SumRowCount

End With

End Sub

 

[Les]

Hi Joel, thanks so much, it would appear to be doing it, but does not see the
empty cells for some reason so always returns a ZERO in column "J" ?

 

[Joel]

Les: Can you put a break point on the two Isempty statements? let me know if
it is getting to neither, one, or both is empty.

click the line with Isempty and then press F9.

there are at least 3 differentt reasons for rereturning zero
1) Lastrow could be wrong
2) PartID may not be matching.
3) IsEmpty is not recognizing the empty cell.

Because one is a stgring and one is a number I think the solution would be
differentt for each

first case "number"
from
If IsEmpty(.Cells(PartRowCount, "N")) Then
to
If not numeric(.Cells(PartRowCount, "N")) Then

second case "text"
from
If IsEmpty(.Cells(PartRowCount, "N")) Then
to
If .Cells(PartRowCount, "N") <> "" Then

 

[Les]

Hi Joel, there is no problem with the "Text" or code but the problem is with
the "Number" i jave played around with the formats and it seems to be working.

Have to go home now, 19:45 here in South Africa, will try in the morning
again. Thanks so much for your help.

 

[Joel]

This is the 1st time someone on the African Continent has said I provided
help. I only need somebody in Antartica to complete all 7.