| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA loop, lookup and calculate problem
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA loop, lookup and calculate problem
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA loop, lookup and calculate problem
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. -- Les "Joel" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA loop, lookup and calculate problem
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" wrote: 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. -- Les "Joel" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA loop, lookup and calculate problem
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" . -- Les "Joel" wrote: 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" wrote: 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. -- Les "Joel" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA loop, lookup and calculate problem
That is what the code does. what is the purpose of the lookup?????
"Les" wrote: 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" . -- Les "Joel" wrote: 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" wrote: 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. -- Les "Joel" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto-calculate after a cell is filled in - macro shouldn't be loop | Excel Programming | |||
| Lookup / Loop / Help Requested | Excel Programming | |||
| Loop & Calculate | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
| Loop expression to calculate return | Excel Programming | |||
Hybrid Mode
