Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Item Count
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Kam Kam is offline
external usenet poster
  
Posts: 57
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
Kam Kam is offline
external usenet poster
  
Posts: 57
Default 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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Kam Kam is offline
external usenet poster
  
Posts: 57
Default 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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Kam Kam is offline
external usenet poster
  
Posts: 57
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to count specific item??? Jeff32 Excel Discussion (Misc queries) 2 May 7th 06 05:38 PM
to How to use Excel formula to count the item annsmjarm Excel Discussion (Misc queries) 1 September 14th 05 12:35 PM
Count number of selected item in Listbox Todd Huttenstine Excel Programming 4 June 14th 04 06:54 PM
count of item in excel workshop Ronald Dodge Excel Programming 0 August 29th 03 08:49 PM
count of item in excel workshop Tom Ogilvy Excel Programming 0 August 29th 03 08:45 PM


All times are GMT +1. The time now is 06:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement