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