Microsoft Excel - Auto Fill Series number

Asked By John on 22-Nov-12 04:14 PM
I have an excel sheet as below. I would like to fill the auto series number just as (1,2,3,4,5,6.....) upto last Name of Items.
Main problem is that number of Items will change. Name of items not constant.  So I need a Macro for put number of items at every time. can someone make a code for me to solve this issue.

Thanks in advance








S.N.

 

 

 

NAME OF ITEMS

 

 

 

Aamlki Rasayan - 100 grm.

 

 

Aamvatari Ras - 20 grm.

 

 

Aarogya Apple Juice 1 ltr.

 

 

Aarogya Basmati Rice

 

 

Aarogya Dalia (Pushtahar) - 500 grm.

 

 

Aarogya Dalia(Wheat) - 500 grm.

 

 

Aarogya Vardhani Vati - 20 grm.

 

 

Aarogya Vardhani Vati - 40 grm.

 

 

Abhayarishta - 450 ml.

 

 

Abhrak Bhasm - 5 grm.

 

 

Absolute Nasal Drop 20 ml.

(Axiom)


Absolute Ointment 50 grm.

(Axiom)


Adusa Juice 500 ml.

(Axiom)


AGARBATTI - ALAKHNANDA

 

 

AGARBATTI - AMBER

 

 

AGARBATTI - MEDITATION

 

 

AGARBATTI - ROSE

 

 

AGARBATTI - SANDAL

 

 

AGARBATTI - UDDH

 

 

AGARBATTI - UTSAV

 

 

AGARBATTI - VANGANDHA

 

 

AGARBATTI - WHITE FLOWER

 

 

AGARBATTI - YAGNA SUGANDHAM

 

 

AGARBATTI- JASMINE

 

 

AGARBATTI- LEVENDER

 

 

Ajmodadi Churn-100 grm.

 

 

Akik Pishti - 5 grm.

 

 

All Rid Oil 60 ml.

(Axiom)


Aloevera Cod 05

(Axiom)


Aloevera Cod 14

(Axiom)


Aloevera Cod 23

(Axiom)


Aloevera Cod 32

(Axiom)


Aloevera Cod Gen. 1000 ml.

(Axiom)


Aloevera Cream 50 grm.

(Axiom)


Aloevera Fiber - 1 ltr.

 

 

Aloevera Gel -150 ml.

 

 

Aloevera Juice 1 ltr.

(Axiom)


Aloevera Juice 500ml

(Axiom)


Aloevera Orange - 1 ltr.

 

 

Aloevera Wheatgerm Lotion

(Axiom)


Aloevera+Amla 1 ltr

(Axiom)


Aloevera+Amla 500ml

(Axiom)


Alovera plain 1 ltr.

 

 

Amaltas Chatni 250 ml.

(Axiom)


Amla Amrit - 65 ml.

 

 

Amla Candy 500 grm.

 

 

Amla Chatpata - 500 grm.

 

 

Amla Churna -100 grm.

 

 

Amla Honey Juice 1000 ml.

(Axiom)


Amla Honey Juice 500 ml.

(Axiom)


Amla Juice 1 ltr.

 

 

Amla Murraba - 1 kg.

 

 

Amla Pickel

 

 

Amla Primum sweet & sour 500grm.

(Axiom)


Amla Pure Juice 1 ltr.

(Axiom)


Amla Pure Juice 500 ml.

(Axiom)


Amla sweet &sour 500 grm.

(Axiom)


Amrit Rasayan 1 kg.

 

 

Amrit Rasayan 500 grm.

 

 

Apamark juice 500 ml.

(Axiom)


Apple Chatney/ Seb Chatney

 

 

Apple Juice 1 ltr.

 

 

Apple Murraba - 1kg.

 

 

Arjun Kwath - 100 grm.

 

 

Arjuna Amla Juice 500 ml.

(Axiom)


Arjunarishta - 450 ml.

 

 

Arogya Vati - 40 grm.

 

 

Arshkalp Vati - 20 grm.

 

 

Arshkalp Vati - 40grm.

 

 

Arvindasava - 225 ml.

 

 

Ashgandha Capsule

 

 

Ashmarihar Kwath

 

 

Ashmarihar Ras - 50 grm.

 

 

Ashokarishta - 450 ml.

 

 

Ashvashila Capsule

 

 

Ashwagandha Churna -100 grm.

 

 

Ashwagandharistha - 450 ml.

 

 

ATTA - 2 kg. (Wheat)

 

 

ATTA - 5 kg. (Wheat)

 

 
Harry Boughen replied to John on 22-Nov-12 11:57 PM
John,

It depends on when you want this to happen.  Do you want it to happen when the operator clicks a button, or when there is a change made in Name of Items column or when the spreadsheet is opened or when the spreadsheet is closed?  Or any other time?

Regards

Harry
Harry Boughen replied to John on 23-Nov-12 12:21 AM
Hello John,

If you add this code to your FindInstances macro before the For Each loop it will renumber your catalog table every time you do a search.

Range("CatalogStart").Offset(0, -1) = 1
Range("CatalogStart").Offset(1, -1) = 2
Range("CatalogStart").Offset(0, -1).Resize(2, 1).AutoFill Destination:=(rngList.Offset(0, -1))

If you want to do it some other time it will require more code.

Regards

Harry
John replied to Harry Boughen on 24-Nov-12 11:22 AM
Yes sir, thank you so much. Its working. Thaks alot