Sorting data/ integer and string 1

[MattSTech]

Hello,
I am trying to sort several integer and string combinations and pass them to an excel file to create a Pareto chart. All is well except for the sort. Here is the scenario:

int1 = 10
int2 = 100
int3 = 15
int4 = 25

str1 = hats
str2 = coats
str3 = mittens
str4 = boots

the integers and strings are associated as such

hats 10
coats 100
mittens 15
boots 25

and the sort is numerical

coats 100
boots 25
mittens 15
hats 10

I would assume an array is the best choice but the examples I have seen here are somewhat confusing as I have never worked with arrays.
I would like the string and integer separated so I can send single values to the excel sheet. Any guidance is appreciated. Thanks.

 

[BiggerBrother]

I think you should look at user defined types. These are similar to arrays, but you can determine the data type for each variable.

BB

 

[dyarwood]

Use

string_stuff = "hats 10"
length_string = Len(string_stuff)
space_position = Mid(string_stuff," &quot
strInfo = Left(string_stuff,space_position)
intInfo = Right(string_stuff,length_string - space_postion+1)

This will give you the string part and the integer part separately. Then you can build arrays up or just pass it into Excel from there. Then just loop it changing what is in string_stuff.

dyarwood

 

[Glasgow]

Or use a (invisible) ListView control with two columns - one with character data and the other with numeric data then sort on numeric column. You may have to format your numeric data with leading zeros for it to sort as you wish.

 

[MattSTech]

Thanks for the prompt replies.

>BiggerBrother
looking into other threads on UDT's

>dyarwood
I am having an issue with declaring space_position (type mismatch)
>Glasgow
The listbox(not listview) is what I started with. then sorted the field by padding the numbers with spaces. I ran into some trouble when two of the integers were identical. I will try the listview with columns and let you know in a couple of minutes.
Again thanks all.

 

[dyarwood]

oops try

space_position = InStr(1,string_stuff, " &quot

instead of the mid

That should get the position.

 

[MattSTech]

>Glasgow

Can you please explain how to sort by the second column, or tell me if i am adding items correctly? No experience with the listview. Thanks

'Adding
lst1.ListItems.Add 1, , str1
lst1.ListItems.Add 2, , int1
lst1.ListItems.Add 1, , str2
lst1.ListItems.Add 2, , int2
lst1.ListItems.Add 1, , str3
lst1.ListItems.Add 2, , int3
lst1.ListItems.Add 1, , str4
lst1.ListItems.Add 2, , int4

'Sorting
lst1.ListItems.Add 1, , str1
lst1.ListItems.Add 2, , int1
lst1.ListItems.Add 1, , str2
lst1.ListItems.Add 2, , int2
lst1.SortKey = ???
lst1.Sorted = True

 

[Glasgow]

You need to use the SubItems collection. Something like this I believe

LsvAll.ListItems.Add , 1, Str1
LsvAll.ListItems(1).SubItems(1) = int1
LsvAll.ListItems.Add , 2, Str2
LsvAll.ListItems(2).SubItems(1) = int2
LsvAll.Sortkey =1

i.e. sort on SubItem no 1.

 

[MattSTech]

I ran the code with the following result:

Run time error '35603'

Invalid Key


Thanks again for the reply

 

[Glasgow]

Sorry - I didn't have time to test. Try something more like

ListView1.ColumnHeaders.Add , "Col1", "Text1"
ListView1.ColumnHeaders.Add , "Col2", "Text2"
ListView1.ListItems.Add 1, , "abc"
ListView1.ListItems(1).SubItems(1) = 15
ListView1.ListItems.Add 2, , "def"
ListView1.ListItems(2).SubItems(1) = 25
ListView1.SortKey = 1

 

[Glasgow]

Or for your example

ListView1.ColumnHeaders.Add , "Col1", "Text1"
ListView1.ColumnHeaders.Add , "Col2", "Text2"
ListView1.ListItems.Add 1, , str1
ListView1.ListItems(1).SubItems(1) = int1
ListView1.ListItems.Add 2, , str2
ListView1.ListItems(2).SubItems(1) = int2
ListView1.SortKey = 1

The .columnheaders.add creates a column for the required subitem.

Your listbox approach should have worked assuming you concatenated the two values - i.e. numeric string plus other string. How do you want it sorted when you have identical values?

 

[MattSTech]

The way I attempted was create 3 list boxes, list box "A" had the String values(unsorted). list box "B" had the numeric values (unsorted). list box "C" had the numeric values (sorted).
I would then cycle through the values in List box "C" and select the item in list box "B" that matched. I would then send that data and the matching list "A" data to the Excel sheet.
However, when I had duplicate nembers is would select the first occurance of that number in list box "B", as well as match that occurance with the improper selection in list "A".
As you can see by the description. It seems to be a backwards approach. I was searching for the cleaner, correct way. I am almost finished adapting your sample to my app. When I am finished (within a few minutes), I will let you know what I have.
Thanks very much for the help.
Matt

 

[Glasgow]

There are probably all sorts of ways of doing this - I was thinking about a single listbox where you build a string like

listbox.additem format(int1,"00000&quot & str1

then sort it

You would have to break up the string later as

int1=left(listbox.list(i),5)
str1=mid(listbox.list(i),6)

Setting up your own ADO recordset would have been another method.

 

[MattSTech]

Well, I have finished. The program now does everything I need.

Glasgow, thank you very much for showing me in the right direction. It is much appreciated. Have a star for the commitment to this thread and showing me the solution.

Matt

(Code)

PadZeros 'Calls a sub to pad zeros to integers

Sub FillList()
'Fill ListView Control with Items

lst1.ColumnHeaders.Add , "Col1", "Text1"
lst1.ColumnHeaders.Add , "Col2", "Text2"
lst1.ListItems.Add 1, , "Hats"
lst1.ListItems(1).SubItems(1) = intHats
lst1.ListItems.Add 2, , "Coats"
lst1.ListItems(2).SubItems(1) = intCoats
lst1.ListItems.Add 3, , "Mittens"
lst1.ListItems(3).SubItems(1) = intMittens
lst1.ListItems.Add 4, , "Boots"
lst1.ListItems(4).SubItems(1) = intBoots

lst1.SortKey = 1
lst1.Sorted = True

strStuff1 = lst1.ListItems.Item(1)
strStuff2 = lst1.ListItems.Item(2)
strStuff3 = lst1.ListItems.Item(3)
strStuff4 = lst1.ListItems.Item(4)

intStuff1 = lst1.ListItems.Item(1).SubItems(1)
intStuff2 = lst1.ListItems.Item(2).SubItems(1)
intStuff3 = lst1.ListItems.Item(3).SubItems(1)
intStuff4 = lst1.ListItems.Item(4).SubItems(1)

End Sub

Sub PadZeros()
'Add Zeros to integers

'intHats
If intHats < 1000 And intHats > 99 Then intHats = &quot;0&quot; & intHats
If intHats < 100 And intHats > 9 Then intHats = &quot;00&quot; & intHats
If intHats < 10 Then intHats = &quot;000&quot; & intHats
'intCoats
If intCoats < 1000 And intCoats > 99 Then intCoats = &quot;0&quot; & intCoats
If intCoats < 100 And intCoats > 9 Then intCoats = &quot;00&quot; & intCoats
If intCoats < 10 Then intCoats = &quot;000&quot; & intCoats
'intMittens
If intMittens < 1000 And intMittens > 99 Then intMittens = &quot;0&quot; & intMittens
If intMittens < 100 And intMittens > 9 Then intMittens = &quot;00&quot; & intMittens
If intMittens < 10 Then intMittens = &quot;000&quot; & intMittens
'intBoots
If intBoots < 1000 And intBoots > 99 Then intBoots = &quot;0&quot; & intBoots
If intBoots < 100 And intBoots > 9 Then intBoots = &quot;00&quot; & intBoots
If intBoots < 10 Then intBoots = &quot;000&quot; & intBoots

End Sub

 

[Glasgow]

Thanks - I'm surprised you haven't been inundated with 'better' solutions.

Using the format function would save you a lot of code - i.e. you don't need the PadZeros if you say
lst1.ListItems(1).SubItems(1) = format (intHats,&quot;000&quot

 

[MattSTech]

Thanks for that, I learn something new every day!.

Matt