Yet another sorting task.

[WebGuy]

Hi !
I have been trying to sort values according to choises made and stored in a value.

Can anyone help me to sort my list in an efficient way and also tell me what i did wrong in my code. Keep in mind that i have only been programming for a couple of weeks, so be gentle...:(

Here is my problem :

I have a list with a number of posts . The list is structured as below.

Patchnamn: Version: Mjukvara: Typ av ?ndring: Server: Omr?de: Ansvar: Kommentar:
Microsoft 4 Windows Patch Alla Alla HYL ABCDEFG
security 4.3. BrightStor Patch Alla Alla HYL asdfjkl

The list is placed in a separate sheet. And a number of options to limit the search can be made from a popup userform on the main spread sheet.
All the choises made in the user form are stored in an array. I tried to write a pice of code that looks at the stored choises in the array and then pick the logs that match the search options and put them in an aarray aswell.

My piece of code works if i search for "all" ie no sorting but not if i specifically search for "HT" or "LT".

I would like to know what i'm doing wrong and how i can make my code work , perhaps you have a better method of sorting lists like this one . Please help me !
:banghead:
Thank you !

This is a part of my code that does not work, where FindLogOpt is the array where the users choises are stored and ShuffleMatrix1 is the array where i want the list items to go.



' Filters the logs according to factory placement (HT, LT, Alla, -).
Select Case FindLogOpt(1)

' Case 1 HT
Case "HT"
While ActiveCell.Value <> "Omr?de:"
If ActiveCell = "HT" Then

ActiveCell.Offset(0, -6).Select
For j = 0 To 8
ShuffleMatrix1(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next j
ActiveCell.Offset(-1, -3).Select
i = i + 1
Else
i = i + 1
ActiveCell.Offset(-1, 0).Select

End If
Wend
' Case 2 LT
Case "LT"
While ActiveCell.Value <> "Omr?de:"
If ActiveCell = "LT" Then

ActiveCell.Offset(0, -6).Select
For j = 0 To 8
ShuffleMatrix1(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next j
ActiveCell.Offset(0, -3).Select

End If
i = i + 1
ActiveCell.Offset(-1, 0).Select
Wend
' Case 3 Alla and no choise
Case Else
While ActiveCell.Value <> "Omr?de:"
ActiveCell.Offset(0, -6).Select
For j = 0 To 8
ShuffleMatrix1(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next j
ActiveCell.Offset(0, -3).Select
i = i + 1
ActiveCell.Offset(-1, 0).Select
Wend
End Select

[OBP]

Have you checked with msgboxes or the immediate window what the values of "i" are when you try to put the values in the array?
Similarly have you printed the array as the loop tries to build it?
Can you you post some example data so that I can look at doing the same thing?

[WebGuy]

]I'll send an excel file with a sample and code.
Maybe this will make it easier to understand .

[OBP]

Webguy, the first thing that is apparent is that this is an ideal Access Database application.
Second is it is very hard to read Swedish :bug:
What is "Logg Sparad"?

[WebGuy]

OBP, I am aware that it would be much better to do this application in access, but excell was decided upon before i got this job , so i can't do anything about that :dunno .
As for swedish .. it's no harder to read than english :*) but log sparad means log saved.
The modules you want are "Find" and "Subs>>Sortarea" Ti think that the "SortArea" sub is the one messing everyting up.
Oh, and too bad England lost against Portugal in football. I would have liked to see England vs Germany in the finals..

[OBP]

Webguy the error is in this piece of code allied with your While and Wend which puts the data on Main. It whiles until ShuffleMatrix1(i, j) = "".

' Case 1 HT
Case "HT"
While ActiveCell.Value <> "Omr?de:"
If ActiveCell = "HT" Then
MsgBox "found it i = " & i & "j = " & j
ActiveCell.Offset(0, -6).Select
For j = 0 To 8
ShuffleMatrix1(i, j) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Next j
ActiveCell.Offset(-1, -3).Select
i = i + 1
Else
'i = i + 1 "REMMED OUT WORKS OK
ActiveCell.Offset(-1, 0).Select

End If

Wend

The else function when it is not "HT" increments "i" which gives you a blank in the array which stops your While/Wend from printing.
Also rather than stepping down "A1" during the search you should use
Range ("A65536).end (xlup).select

or go straight to column G with
Range ("G65536).end (xlup).select

[WebGuy]

Yes! Great !
It works nicely now! I don't know why it's so hard to spot ones own errors...
Thanks a lot ..
As for "Range ("G65536).end (xlup).select" what does that do exactly ? what does it mean ? syntax ? What are the pros /cons of using this ?

[OBP]

It finds the last cell in the column with data in it.

[WebGuy]

Ok ! Thanks alot for all the help!.:beerchug: