Item Count

[Kam]

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.

 

[Kam]

****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.

 

[Kam]

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

 

[Joel]

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]

Thank you very much.

It's worked.

Kam.

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