Sort sheet A-Z advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,953
Office Version
  1. 2007
Platform
  1. Windows
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




VBA Code:
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
 
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
 
Upvote 0
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.
 

Attachments

  • EaseUS_2025_02_26_16_28_12.jpg
    EaseUS_2025_02_26_16_28_12.jpg
    62.6 KB · Views: 3
  • EaseUS_2025_02_26_16_29_24.jpg
    EaseUS_2025_02_26_16_29_24.jpg
    50 KB · Views: 3
  • EaseUS_2025_02_26_16_29_52.jpg
    EaseUS_2025_02_26_16_29_52.jpg
    48.6 KB · Views: 3
Upvote 0
Are the values in column A-L all hard-coded, or are there any formulas in any of the columns?
 
Upvote 0
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
 

Attachments

  • EaseUS_2025_02_26_16_59_09.jpg
    EaseUS_2025_02_26_16_59_09.jpg
    31.9 KB · Views: 1
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)?
Excel Formula:
=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:
Rich (BB code):
.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
to this:
Rich (BB code):
.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlYes
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top