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]

Here we go,

Column A = MIXTURE EX CIVIC 06-13

Column B = YEAR 1990-2015

Column C = NUMBER 1-3

Column D = MIXTURE EX ID47

Column E = NUMBER 0-3

Column F = MIXTURE EX CE0523

 

[Fluff]

Ok, replace this

    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

with

    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
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    End With

 

[ipbr21054]

Now End With Without With

 

[Fluff]

Missed the Next i

    With ThisWorkbook.Worksheets("SKPLIST")
        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
            Select Case i
               Case 1, 2, 4
                  .Cells(4, i + 1) = Val(ControlsArr(i))
                  ControlsArr(i).Text = ""
               Case Else
                  .Cells(4, i + 1) = ControlsArr(i)
                  ControlsArr(i).Text = ""
            End Select
        [COLOR=#ff0000] Next i[/COLOR]
    End With

 

[ipbr21054]

Many thanks for that it worked great.

I have also copied it to another sheet which uses the same code.
Ive changed the obvious items butm when i transfer from form to sheet i see some incorrect values.

I believe the answer lies in the code part below but could not work it out.

    With ThisWorkbook.Worksheets("CLONING")        .Range("A4").EntireRow.Insert Shift:=xlDown
        .Range("A4:F4").Borders.Weight = xlThin
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(4, i + 1) = Val(ControlsArr(i))
               ControlsArr(i).Text = ""
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select

So like before here is the info for these columns

COLUMN A = MIXTURE EX 4D-63 40 BIT

COLUMN B = JUST WORDS NO NUMBERS

COLUMN C = MIXTURE EX CN1 CERAMIC

COLUMN D = YEAR ONLY EX 1990 - 2015

COLUMN E = MIXTURE EX RAV 4

COLUMN F = MIXTURE EX CN 900

The reason i ask is because of the following

Cell B4 should of been CERAMIC but i actually see 0

Cell C5 should of been KING CHIP but i actually see 4.00E-60

Cell E6 should of been HONDA ACCORD but i actually see 0

Many thanks for your time.

 

[Fluff]

This

Case 1, 2, 4

needs to be

Case 3

 

[ipbr21054]

Thanks that worked.
can you tell me what 12,4 & 3 relate to

 

[Fluff]

They represent the index number for the array, so with this array

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

Combo1 is index 0, combo2 is index 1 etc
So with the code in post#24 It will do one thing for comboboxes 2,3 & 5 & something else for the others.