Userform to worksheet cell align left advice

[ipbr21054]

Hi,
Im using the code below of which works for me apart from one thing.

When i send values from userform to worksheet i wish the text in cell column N to be aligned Left but it keeps centering it.
I thought my line of code in red below would fix it but its either wrong or isnt working.
I see no errors when this code is run

Please advise

Private Sub CommandButton1_Click()
    Dim i           As Integer
    Dim LastRow     As Long
    Dim wsGIncome   As Worksheet
    Dim arr(1 To 5) As Variant
    Dim Prompt      As String
  
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
  
    For i = 1 To 5
        Prompt = Choose(i, "CUSTOMERS'S NAME", "ADDRESS", "POST CODE", "CHARGE", "MILEAGE")
        With Me.Controls("TextBox" & i)
            If Len(.Value) = 0 Then
                MsgBox "NO " & Prompt & " & WAS ENTERED", 16, Prompt & " Empty MESSAGE"
                .SetFocus
                Exit Sub
            Else
                If InStr(1, .Value, "£") = 1 Then
                    arr(i) = CCur(.Value)
                ElseIf IsDate(.Value) Then
                    arr(i) = DateValue(.Value)
                ElseIf IsNumeric(.Value) Then
                    arr(i) = Val(.Value)
                Else
                    arr(i) = .Value
                End If
            End If
          
        End With
    Next i
      
         Application.ScreenUpdating = False
        With wsGIncome
            LastRow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
          
            With .Cells(LastRow, 14).Resize(, UBound(arr))
                .Value = arr
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.Weight = xlThin
                .Interior.ColorIndex = 6
                .Cells(LastRow, "N").HorizontalAlignment = xlLeft
            End With
         
            .Range("O4").Select
          
        End With

       Unload Me
        Application.ScreenUpdating = True
         MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
         
         ActiveSheet.Sort.SortFields.Clear
         ActiveSheet.Sort.SortFields.Add Key:=Range("N1"), _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("N4:R38")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    End Sub

 

[bobsan42]

the line in red refers to column 14 (N) in the range .Cells(LastRow, 14).Resize(, UBound(arr))
So it probably formats the 14th cell in the range.
to format the first cell in the range change it to:

                .Cells(1, 1).HorizontalAlignment = xlLeft

or move the row in red one row down - after End With

 

[ipbr21054]

Thatnks.
The vba code you advised worked a treat.
Have a nice day