Still unable to locate the case of a invalid sort

[ipbr21054]

Hi,
Ive touched on this before but never solved it so now having a clear head im trying once again.

I have a userform which once the fields are completed i press CommandButton1 which transfers the data to my worksheet & then sorts column A from
A-Z so far this is ok.

My problem is that column B C E are numbers.
So lets say cell B49 has the value 1991 in it.
In the cell at the top left corner is a small marker.

I also have filters so if i use the code at the bottom of this post "which is on a button" to sort the year from oldest "top of page" to newest "going down the page" the year 1991 is last.
All the years are correct down the worksheet apart from 1991 being last.

The same issue also resides for column C and E of which are also numbers

USERFORM TO WORKSHEET CODE

Private Sub CommandButton1_Click()    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 6
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", 48, "SKP IMMO LIST TRANSFER"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.ComboBox3, Me.ComboBox4, Me.ComboBox5, Me.ComboBox6)
    
    With ThisWorkbook.Worksheets("SKPLIST")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        .Range("A4:F4").Value = ControlsArr
    End With
    
    For Each ctrl In ControlsArr
        ctrl.Text = ""
    Next
    
    Application.ScreenUpdating = False
    With Sheets("SKPLIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A3:F" & x).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    Sheets("SKPLIST").Range("A4").Select
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
    Me.ComboBox1.SetFocus
End Sub

SORT FILTER

Private Sub ImmoYearButton_Click()    
    Dim x As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("SKPLIST")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Range("A3:F" & x).Sort key1:=Range("B4"), order1:=xlAscending, Header:=xlGuess
    
    End With
                      
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    Sheets("SKPLIST").Range("A4").Select
    
End Sub

 

[ipbr21054]

You are correct and now i have found the merged cell.
I have now unmerged it & run the last code which tells me No Merged Cells Found.

I run that other code again but still same message as in post #7

 

[MARK858]

You don't need the other code now, you need to do the text to columns in post 4 then try your sort again.

 

[ipbr21054]

When i transefer from form to sheet i still get that marker top left corner of cell.

I do the sort using my button but still the year for this entry is out of date.

I manually type the same number in the cell and i notice that the marker has now gone.
I do the sort with my button and the sort is correct.

 

[MARK858]

Did you do the text to columns from post 4 on the column with the cell with the green marker?

You also need to make sure that all your cells in the sheet you are transferring to are formatted as General and Not Text before the transfer.

 

[ipbr21054]

Hi,
Yes to text to columns
Yes to format all cells to general.

Still when i transfer the marker appears on that new row.

I have taken up to much of your time with this so i will leave it now & continue as such

Many thanks

 

[Fluff]

Try

ControlsArr = Array(Me.ComboBox1, Val(Me.ComboBox2), Val(Me.ComboBox3), Me.ComboBox4, Val(Me.ComboBox5), Me.ComboBox6)

 

[ipbr21054]

Fluff please advise where I put that.

 

[Fluff]

Just change that line in you code as I showed.

 

[ipbr21054]

Fluff with the line now used i get a run time error 424 Object Required.
when i debug i see this in yellow.

ctrl.Text = ""

 

[Fluff]

Didn't notice that part.
Are all the controls numerical values, or just some?