Sort sheet A-Z advice please

[ipbr21054]

The code in use is supplied below.

The problem i see now & then is column L is out of sync.
Column K in various cells have a colour written in text example BLACK
Column L matches it, So if K30 is BLUE then L30 should also be BLUE

Sometimes when i use this sheet i notice a cell in column L is empty but the cell in column K has say CLEAR in it ????

So info for you.
Headers in row 6
Row 7 is hidden
Range is A6-L6 then down the page

Do you see an issue in the code below please

Private Sub PressToSort_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("MCLIST")
Dim SortColumn As String

Select Case ComboBox1

Case "CUSTOMER"
SortColumn = "A"

Case "FRAME NUMBER"
SortColumn = "B"

Case "MAKE"
SortColumn = "C"

Case "MODEL"
SortColumn = "D"

Case "ITEM BOUGHT"
SortColumn = "E"

Case "CHIP"
SortColumn = "F"

Case "COUNTRY"
SortColumn = "G"

Case "ORIGINAL PN"
SortColumn = "H"

Case "YEAR"
SortColumn = "I"

Case "LEAD"
SortColumn = "J"

Case "LEAD TYPE"
SortColumn = "K"

Case "CONNECTOR USED"
SortColumn = "L"

End Select
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
Range("A6").Select
Range("A8").Select
End With
End If
Unload AtoZMotorcycles
End Sub

 

[Joe4]

For an example that is not working the way you would like, please include three images so we can see what you are working with and what you want to happen:
1. What your data looks like to start
2. What your data looks like AFTER the code runs
3. What your data SHOULD look like after the code runs

 

[ipbr21054]

See attached.

The issue is column L isnt in sync with column K
Basically the text colours should match in K & L but sometimes its out of sync like my made up example.

 

[Joe4]

Are the values in column A-L all hard-coded, or are there any formulas in any of the columns?

 

[ipbr21054]

I complete a userform & then the values are sent to worksheet.

How can i check if any formulas etc.
I do see conditional formatting as shown which just gives UK or USA a colour

 

[Joe4]

What do you mean you don't know if the data in rows 8 to the bottom has formulas in it or not?
Just look at the cells, and see if they contain formulas or hard-coded values.
We cannot check that for you - you have not given us access to the sheet.

Does the Userform populate every single column with hard-coded values (A-L), or are there any formulas in any of those columns (A-L)?

 

[ipbr21054]

Clicking in each cell & looking in address bar i see no formulas mentioned.

The form will put values in cells A-K
I put a value in cell L is need be manually

 

[Joe4]

So I am not sure I understand your question. Parts of what you said are very confusing, such as:

Column L matches it, So if K30 is BLUE then L30 should also be BLUE

but then you said:

I put a value in cell L is need be manually

So column L is supposed to match column K, but you are entering column L manually?
I don't understand.

Or is the issue the coloring of certain cells?
But your code is not doing any coloring of cells, it looks like it is sorting.
So I am very confused as to what you are trying to do.

Can you explain it in more detail, in relation to the screen images you posted?
And in your example, what are you chooding for your sort column when you run your code?

 

[ipbr21054]

Correct if K22 is BLACK then L22 should
also be BLACK.
I enter it manually as wasn’t sure how to code the form.
Also there are values like N/A & BUNDLE

Same goes for coding Red other values. Didn’t want to mess it up or confuse the issue so was happy to enter it manually.


In my example I have a sort option where the user can select any of the headers.

I’ve played with it before & see all is fine.
Next time I sort I then see it’s out of sync.

 

[Joe4]

Correct if K22 is BLACK then L22 should
also be BLACK.
I enter it manually as wasn’t sure how to code the form.
Also there are values like N/A & BUNDLE

If you want column L to match column K, why not just put a formula in column L, like this (for row 22)?

=K22

Then that will always ensure that they match.

If your data is being populated (columna A-K) from VBA code in your UserForm, you could easily amend your VBA to place the formula in column L for every row of data it is adding.

In my example I have a sort option where the user can select any of the headers.

I’ve played with it before & see all is fine.
Next time I sort I then see it’s out of sync.

OK, I am trying to recreate your scenario here on my side, so I need a VERY detailed description of an example that does NOT work.
1. So, I was to see what your data looks like at the start.
2. Then, I want to know which field you are asking to sort by, and then what your data looks like after the code runs.
3. I want to see how the data in item 2 above is wrong, and what it should look like.

One minor change that might help is to change the last word here:

.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

to this:

.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlYes