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