automatically auto numbering in multiple columns based on fill number

[KalilMe]

hi
I would auto numbering each column contain word of header is ITEM what I write type of number then should when press the next cell autonumber and so on to reach a different number
first

00 (1).xlsx
	E	F	G	H	I	J
1	item	brand	item	brand	item	brand
2	bsd/12	oli1-12	fj-1	water 1	ch/jj00-1	cheese1
3		oli1-13		water 2		cheese2
4		oli1-14		water 3		cheese3
5		oli1-15		water 4		cheese4
6		oli1-16		water 5		cheese5
7		oli1-17		water 6		cheese6
8		oli1-18		water 7		cheese7
9		oli1-19		water 8		cheese8
10		oli1-20		water 9		cheese9
11		oli1-21		water 10		cheese10
12	ss-210	tuna1-1		water 11		cheese11
13		tuna1-2		water 12		cheese12
14		tuna1-3	10-s001	drink1		cheese13
15		tuna1-4		drink2		cheese14
16		tuna1-5		drink3		cheese15
17		tuna1-6		drink4		cheese16
18	fd-12	food1		drink5		cheese17
19		food2		drink6	bt-200	butter1
20		food3		drink7		butter2
21		food4		drink8		butter3
22		food5		drink9		butter4
23		food6		drink10		butter5
24		food7		drink11		butter6
25		food8		drink12		butter7
26		food9		drink13		butter8
27		food10		drink14		butter9
28		food11		drink15		butter10
29		food12		drink16		butter11
1

expected result

00 (1).xlsx
	E	F	G	H	I	J
1	item	brand	item	brand	item	brand
2	bsd/12	oli1-12	fj-1	water 1	ch/jj00-1	cheese1
3	bsd/13	oli1-13	fj-2	water 2	ch/jj00-2	cheese2
4	bsd/14	oli1-14	fj-3	water 3	ch/jj00-3	cheese3
5	bsd/15	oli1-15	fj-4	water 4	ch/jj00-4	cheese4
6	bsd/16	oli1-16	fj-5	water 5	ch/jj00-5	cheese5
7	bsd/17	oli1-17	fj-6	water 6	ch/jj00-6	cheese6
8	bsd/18	oli1-18	fj-7	water 7	ch/jj00-7	cheese7
9	bsd/19	oli1-19	fj-8	water 8	ch/jj00-8	cheese8
10	bsd/20	oli1-20	fj-9	water 9	ch/jj00-9	cheese9
11	bsd/21	oli1-21	fj-10	water 10	ch/jj00-10	cheese10
12	ss-210	tuna1-1	fj-11	water 11	ch/jj00-11	cheese11
13	ss-211	tuna1-2	fj-12	water 12	ch/jj00-12	cheese12
14	ss-212	tuna1-3	10-s001	drink1	ch/jj00-13	cheese13
15	ss-213	tuna1-4	10-s002	drink2	ch/jj00-14	cheese14
16	ss-214	tuna1-5	10-s003	drink3	ch/jj00-15	cheese15
17	ss-215	tuna1-6	10-s004	drink4	ch/jj00-16	cheese16
18	fd-12	food1	10-s005	drink5	ch/jj00-17	cheese17
19	fd-13	food2	10-s006	drink6	bt-200	butter1
20	fd-14	food3	10-s007	drink7	bt-201	butter2
21	fd-15	food4	10-s008	drink8	bt-202	butter3
22	fd-16	food5	10-s009	drink9	bt-203	butter4
23	fd-17	food6	10-s010	drink10	bt-204	butter5
24	fd-18	food7	10-s011	drink11	bt-205	butter6
25	fd-19	food8	10-s012	drink12	bt-206	butter7
26	fd-20	food9	10-s013	drink13	bt-207	butter8
27	fd-21	food10	10-s014	drink14	bt-208	butter9
28	fd-22	food11	10-s015	drink15	bt-209	butter10
29	fd-23	food12	10-s016	drink16	bt-210	butter11
ة1

 

[igold]

Does this do what you want...

Sub Food()

    Dim lCol As Long, i As Long, lRow As Long, lRow2 As Long, ct As Long, x As Long
    Dim itm As String

    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 5 To lCol
        If Cells(1, i) = "item" Then
            lRow = Cells(Rows.Count, i + 1).End(xlUp).Row
            ct = Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(lRow, i)))
            For x = 1 To ct
                lRow2 = Cells(lRow, i).End(xlUp).Row
                itm = Cells(lRow2, i).Value
                Cells(lRow2, i).Resize(lRow - lRow2 + 1) = itm
                lRow = lRow2 - 1
            Next
        End If
    Next

End Sub

 

[KalilMe]

thanks
this is what I got after run the code

00 (1).xlsx
	E	F	G	H	I	J
1	item	brand	item	brand	item	brand
2	bsd/12	oli1-12	fj-1	water 1	ch/jj00-1	cheese1
3	bsd/12	oli1-13	fj-1	water 2	ch/jj00-1	cheese2
4	bsd/12	oli1-14	fj-1	water 3	ch/jj00-1	cheese3
5	bsd/12	oli1-15	fj-1	water 4	ch/jj00-1	cheese4
6	bsd/12	oli1-16	fj-1	water 5	ch/jj00-1	cheese5
7	bsd/12	oli1-17	fj-1	water 6	ch/jj00-1	cheese6
8	bsd/12	oli1-18	fj-1	water 7	ch/jj00-1	cheese7
9	bsd/12	oli1-19	fj-1	water 8	ch/jj00-1	cheese8
10	bsd/12	oli1-20	fj-1	water 9	ch/jj00-1	cheese9
11	bsd/12	oli1-21	fj-1	water 10	ch/jj00-1	cheese10
12	ss-210	tuna1-1	fj-1	water 11	ch/jj00-1	cheese11
13	ss-210	tuna1-2	fj-1	water 12	ch/jj00-1	cheese12
14	ss-210	tuna1-3	10-s001	drink1	ch/jj00-1	cheese13
15	ss-210	tuna1-4	10-s001	drink2	ch/jj00-1	cheese14
16	ss-210	tuna1-5	10-s001	drink3	ch/jj00-1	cheese15
17	ss-210	tuna1-6	10-s001	drink4	ch/jj00-1	cheese16
18	fd-12	food1	10-s001	drink5	ch/jj00-1	cheese17
19	fd-12	food2	10-s001	drink6	bt-200	butter1
20	fd-12	food3	10-s001	drink7	bt-200	butter2
21	fd-12	food4	10-s001	drink8	bt-200	butter3
22	fd-12	food5	10-s001	drink9	bt-200	butter4
23	fd-12	food6	10-s001	drink10	bt-200	butter5
24	fd-12	food7	10-s001	drink11	bt-200	butter6
25	fd-12	food8	10-s001	drink12	bt-200	butter7
26	fd-12	food9	10-s001	drink13	bt-200	butter8
27	fd-12	food10	10-s001	drink14	bt-200	butter9
28	fd-12	food11	10-s001	drink15	bt-200	butter10
29	fd-12	food12	10-s001	drink16	bt-200	butter11
1

this is not the same image2 it should increment

 

[igold]

My bad, how about this...

Sub Food2()

    Dim lCol As Long, i As Long, lRow As Long, lRow2 As Long, ct As Long, x As Long
    Dim itm As String
    
    Application.ScreenUpdating = False
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 5 To lCol
        If Cells(1, i) = "item" Then
            lRow = Cells(Rows.Count, i + 1).End(xlUp).Row
            ct = Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(lRow, i)))
            For x = 1 To ct
                lRow2 = Cells(lRow, i).End(xlUp).Row
                Cells(lRow2, i).Select
                Selection.AutoFill Destination:=Range(Selection, Cells(lRow, i)), Type:=xlFillDefault
                lRow = lRow2 - 1
            Next
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[KalilMe]

yes ! that's great
many thanks for your nice code

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!

 

[KalilMe]

sorry I come back again but the code need fixing if I write 1 it doesn't increment 2,3... it copies the same value it shows 1,1,1 and if I repeat running macro it gives error "application defined or object defined error " in this line

lRow2 = Cells(lRow, i).End(xlUp).Row

and replace word ITEM for all numbers . may you fix it please ?

 

[igold]

This will take care of the event that you have a number and not a string to be filled. However the code as written was designed to only be run once on any data set as provided in your sample. The only way for anyone to write code for every possibility that you may have is to provide a more comprehensive example of your needs. I have added a line that will force the sub to exit before it writes the word "item" down the column.

Sub Food2()

    Dim lCol As Long, i As Long, lRow As Long, lRow2 As Long, ct As Long, x As Long
    Dim itm As String
    
    Application.ScreenUpdating = False
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 5 To lCol
        If Cells(1, i) = "item" Then
            lRow = Cells(Rows.Count, i + 1).End(xlUp).Row
            ct = Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(lRow, i)))
            For x = 1 To ct
                lRow2 = Cells(lRow, i).End(xlUp).Row
                If lRow2 = 1 Then
                    MsgBox "Invalid Column Found - Sub Will Now Exit"
                    Exit Sub
                End If
                Cells(lRow2, i).Select
                If Application.WorksheetFunction.IsNumber(Selection) Then
                    Selection.AutoFill Destination:=Range(Selection, Cells(lRow, i)), Type:=xlFillSeries
                Else
                    Selection.AutoFill Destination:=Range(Selection, Cells(lRow, i)), Type:=xlFillDefault
                End If
                lRow = lRow2 - 1
            Next
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[KalilMe]

thanks again for your cooperation

 

[igold]

You're welcome. Thanks again for the feedback.