Creating a sorted list with Excel VBA

[vestax20]

Hi, using Excel XP

I have a small vba applicaiton that loads a form on start. This form is used to add clients. I want to update an existing list located on a worksheet of the same workbook with a new entry. This entry has to be placed in the correct alphabetic location in the list. Here's an example.

This is the ID that will be entered which is followed by other criteria :

cous22088006

now in the existing database there are many other entries. This entry has to be placed in the correct location inside the list like this


aaaa22085544
bbbb22056555
cous22088006
dddd54445589

and so on

Any insights?

Keep in mind that there is other data retrieved from the form that will be placed along side the number above.

Thx a lot!!!

 

[Loomah]

Hi
Add your new data to the end of the list then sort the data.
Or am I missing something over simplifying your problem?

Your sort code would be something like this (recorded)

Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[vestax20]

Thx for the reply Loomah.

Remember that I'm not one field but all the fields in the same line. And why range("A1&quot? Wouldn't that just sort one cell?


Thx for the reply

 

[RobBroekhuis]

Loomah is just giving an example. You're right that it would be appropriate to explicitly specify the range to sort (although Excel usually does a good job figuring this out). If you have a named range "MyData" which covers all your rows and columns, say A1:I99, and one header row (row 1), you can add your new data by

rows(2).insert
...put your new data in the fields on row 2...

MyData will now have updated to refer to A1:I100, and you can sort using

range("MyData&quot.sort key1:=range("MyData&quot.cells(1,1), header:=xlYes



Rob

 

[rdodge]

Be careful about this inserting business cause if your range is only 1 row down cause it only has 1 row of data (or no data if that one row is the header row), using the insertion as Rob mention would not work cause you can't insert between 2 rows within the named range of 1 row. Even though it's not near as likely to happen in your case, but though I would just point it out. The other thing you should be careful about, there are only 65536 available rows for any one worksheet, so if your code attempts to insert something that would cause it to exceed 65536, it will error out.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RobBroekhuis]

Ronald is right - if you have NO data (i.e., only a header row), the approach won't work as written (although it's easy enough to check for this eventuality and take appropriate measures accordingly).
As for the 65536 maximum - I'd hope that an application different from Excel would have been selected if the number of records was anticipated to get quite so large...


Rob