Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Item Count
Hi,
Happy new year to you all!! Thanks for your all help in the last year. This is my first request of the year on which I want your help. It is very difficult for me to explain in detail. Basically I want you to give VBA code which can count all the items in column A until next non blank cell & return the value in column C & fill that data until row where "TOTAL" is mentioned. For your easy ref. I have made the before & after data. BEFORE AFTER Container No. Item Container No. Item Count MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 50 50 3 10 10 3 TOTAL TOTAL 3 MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 10 10 3 TOTAL TOTAL 3 TTNU2298269 30 TTNU2298269 30 2 TTNU2298270 40 TTNU2298270 40 2 TTNU2298271 20 TTNU2298271 20 2 50 50 2 TOTAL TOTAL 2 CAXU6315919 30 CAXU6315919 30 1 40 40 1 20 20 1 TOTAL TOTAL 1 Is this possible? Thanks & Best Regards, Kam. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Item Count
****PLEASE IGNORE MY PREVIOUS COMMMENTS****
Considered this one. Hi, Happy new year to you all!! Thanks for your all help in the last year. This is my first request of the year on which I want your help. It is very difficult for me to explain in detail. Basically I want you to give VBA code which can count all the items in column A until next non blank cell & return the value in column C & fill that data until row where "TOTAL" is mentioned. For your easy ref. I have made the before & after data. BEFORE AFTER Container No. Item Container No. Item Count MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 50 50 3 10 10 3 TOTAL TOTAL 3 MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 10 10 3 TOTAL TOTAL 3 TTNU2298269 30 TTNU2298269 30 2 TTNU2298270 40 TTNU2298270 40 2 20 TTNU2298271 20 2 50 50 2 TOTAL TOTAL 2 CAXU6315919 30 CAXU6315919 30 1 40 40 1 20 20 1 TOTAL TOTAL 1 Is this possible? Thanks & Best Regards, Kam. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Item Count
I have below which does the couting for me.
Sub ItemCount() Range("A1").Select ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, 0).Activate Selection.End(xlToLeft).Select ActiveCell.Offset(0, 12).Activate LastInCol = ActiveCell.Address ActiveCell.Value = "End" Range("M2").Select Do While ActiveCell.Address < LastInCol ActiveCell.Offset(0, -4).Activate If ActiveCell.Value = "TOTAL" Then ActiveCell.Offset(0, 4).Activate ActiveCell.Value = "Here" Else ActiveCell.Offset(0, 4).Activate End If ActiveCell.Offset(1, 0).Activate Loop Range("M2").Select ActiveCell.Offset(0, -6).Activate add1 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Do While ActiveCell.Value < "End" ActiveCell.Formula = "=COUNTA(" & add1 & ":" & add2 & ")" add1 = add2 Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Loop Selection.ClearContents End Sub "Kam" wrote: ****PLEASE IGNORE MY PREVIOUS COMMMENTS**** Considered this one. Hi, Happy new year to you all!! Thanks for your all help in the last year. This is my first request of the year on which I want your help. It is very difficult for me to explain in detail. Basically I want you to give VBA code which can count all the items in column A until next non blank cell & return the value in column C & fill that data until row where "TOTAL" is mentioned. For your easy ref. I have made the before & after data. BEFORE AFTER Container No. Item Container No. Item Count MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 50 50 3 10 10 3 TOTAL TOTAL 3 MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 10 10 3 TOTAL TOTAL 3 TTNU2298269 30 TTNU2298269 30 2 TTNU2298270 40 TTNU2298270 40 2 20 TTNU2298271 20 2 50 50 2 TOTAL TOTAL 2 CAXU6315919 30 CAXU6315919 30 1 40 40 1 20 20 1 TOTAL TOTAL 1 Is this possible? Thanks & Best Regards, Kam. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Item Count
Your posted code is vewry hard to follow where you use active cells and
offsets. I recommend you modify the code like the code below to specifically reference actual column whre possible. Sub CountItems() LastRow = Range("C" & Rows.Count).End(xlUp).Row ItemCount = 0 StartRow = 2 FirstRow = StartRow ''first row of a group FirstUpdate = True For RowCount = StartRow To LastRow If Range("A" & RowCount) < "" Then ItemCount = ItemCount + 1 Else If FirstUpdate = True Then For UpdateRow = FirstRow To (RowCount - 1) Range("C" & UpdateRow) = ItemCount Next UpdateRow FirstUpdate = False End If Range("D" & RowCount) = ItemCount If Range("C" & RowCount) = "TOTAL" Then FirstRow = RowCount + 1 FirstUpdate = True ItemCount = 0 End If End If Next RowCount End Sub "Kam" wrote: I have below which does the couting for me. Sub ItemCount() Range("A1").Select ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, 0).Activate Selection.End(xlToLeft).Select ActiveCell.Offset(0, 12).Activate LastInCol = ActiveCell.Address ActiveCell.Value = "End" Range("M2").Select Do While ActiveCell.Address < LastInCol ActiveCell.Offset(0, -4).Activate If ActiveCell.Value = "TOTAL" Then ActiveCell.Offset(0, 4).Activate ActiveCell.Value = "Here" Else ActiveCell.Offset(0, 4).Activate End If ActiveCell.Offset(1, 0).Activate Loop Range("M2").Select ActiveCell.Offset(0, -6).Activate add1 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Do While ActiveCell.Value < "End" ActiveCell.Formula = "=COUNTA(" & add1 & ":" & add2 & ")" add1 = add2 Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Loop Selection.ClearContents End Sub "Kam" wrote: ****PLEASE IGNORE MY PREVIOUS COMMMENTS**** Considered this one. Hi, Happy new year to you all!! Thanks for your all help in the last year. This is my first request of the year on which I want your help. It is very difficult for me to explain in detail. Basically I want you to give VBA code which can count all the items in column A until next non blank cell & return the value in column C & fill that data until row where "TOTAL" is mentioned. For your easy ref. I have made the before & after data. BEFORE AFTER Container No. Item Container No. Item Count MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 50 50 3 10 10 3 TOTAL TOTAL 3 MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 10 10 3 TOTAL TOTAL 3 TTNU2298269 30 TTNU2298269 30 2 TTNU2298270 40 TTNU2298270 40 2 20 TTNU2298271 20 2 50 50 2 TOTAL TOTAL 2 CAXU6315919 30 CAXU6315919 30 1 40 40 1 20 20 1 TOTAL TOTAL 1 Is this possible? Thanks & Best Regards, Kam. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Item Count
Thank you very much.
It's worked. Kam. "Joel" wrote: Your posted code is vewry hard to follow where you use active cells and offsets. I recommend you modify the code like the code below to specifically reference actual column whre possible. Sub CountItems() LastRow = Range("C" & Rows.Count).End(xlUp).Row ItemCount = 0 StartRow = 2 FirstRow = StartRow ''first row of a group FirstUpdate = True For RowCount = StartRow To LastRow If Range("A" & RowCount) < "" Then ItemCount = ItemCount + 1 Else If FirstUpdate = True Then For UpdateRow = FirstRow To (RowCount - 1) Range("C" & UpdateRow) = ItemCount Next UpdateRow FirstUpdate = False End If Range("D" & RowCount) = ItemCount If Range("C" & RowCount) = "TOTAL" Then FirstRow = RowCount + 1 FirstUpdate = True ItemCount = 0 End If End If Next RowCount End Sub "Kam" wrote: I have below which does the couting for me. Sub ItemCount() Range("A1").Select ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(1, 0).Activate Selection.End(xlToLeft).Select ActiveCell.Offset(0, 12).Activate LastInCol = ActiveCell.Address ActiveCell.Value = "End" Range("M2").Select Do While ActiveCell.Address < LastInCol ActiveCell.Offset(0, -4).Activate If ActiveCell.Value = "TOTAL" Then ActiveCell.Offset(0, 4).Activate ActiveCell.Value = "Here" Else ActiveCell.Offset(0, 4).Activate End If ActiveCell.Offset(1, 0).Activate Loop Range("M2").Select ActiveCell.Offset(0, -6).Activate add1 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Do While ActiveCell.Value < "End" ActiveCell.Formula = "=COUNTA(" & add1 & ":" & add2 & ")" add1 = add2 Selection.End(xlDown).Select ActiveCell.Offset(0, -6).Activate add2 = ActiveCell.Address ActiveCell.Offset(0, 6).Activate Loop Selection.ClearContents End Sub "Kam" wrote: ****PLEASE IGNORE MY PREVIOUS COMMMENTS**** Considered this one. Hi, Happy new year to you all!! Thanks for your all help in the last year. This is my first request of the year on which I want your help. It is very difficult for me to explain in detail. Basically I want you to give VBA code which can count all the items in column A until next non blank cell & return the value in column C & fill that data until row where "TOTAL" is mentioned. For your easy ref. I have made the before & after data. BEFORE AFTER Container No. Item Container No. Item Count MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 50 50 3 10 10 3 TOTAL TOTAL 3 MSKU3569932 30 MSKU3569932 30 3 MSKU3569933 40 MSKU3569933 40 3 MSKU3569934 20 MSKU3569934 20 3 10 10 3 TOTAL TOTAL 3 TTNU2298269 30 TTNU2298269 30 2 TTNU2298270 40 TTNU2298270 40 2 20 TTNU2298271 20 2 50 50 2 TOTAL TOTAL 2 CAXU6315919 30 CAXU6315919 30 1 40 40 1 20 20 1 TOTAL TOTAL 1 Is this possible? Thanks & Best Regards, Kam. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count specific item??? | Excel Discussion (Misc queries) | |||
to How to use Excel formula to count the item | Excel Discussion (Misc queries) | |||
Count number of selected item in Listbox | Excel Programming | |||
count of item in excel workshop | Excel Programming | |||
count of item in excel workshop | Excel Programming |