Code not working as it should

[ipbr21054]

Morning,
I have a userform which after selecting from a drop down list its then sent to my worksheet.
ComboBox5 has the options as follows,
1
2
3
OLD 3 PIN PLUG

This is then sent to the cell in column E
My problem is that when the OLD 3 PIN PLUG is selected the transfered data in the cell reads 0 and not OLD 3 PIN PLUG.

This is the code for the transfer button.

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
        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
    Next i
    End With
    
    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

The cells on the worksheet are all formatted as General

Do you see why this is happening.

Many Thanks.

 

[Fluff]

It's because this

Val(ControlsArr(i))

is trying to convert the value into a number

 

[ipbr21054]

Hi,
How can this be fixed so numbers & letters are taken into account.

 

[Fluff]

Try

               .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))

 

[ipbr21054]

This,
I put the advised code in the place as shown below & it worked.

Many Thanks

        For i = 0 To UBound(ControlsArr)         Select Case i
            Case 1, 2, 4
              [COLOR=#ff0000] .Cells(4, i + 1) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))[/COLOR]
            Case Else
               .Cells(4, i + 1) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select

 

[Fluff]

You're welcome & thanks for the feedback