ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the code shown below.
I enter details on my userform then pressing the command button sends those details to my worksheet & sorts column A-Z
A couple of times now it doesnt sort.
Ive just added a new customer for which is M HOUSE G + S.
It should of been sorted so its at N23 but its last at N32
Can you see an issue in the code.
My current worksheet where the details are stored is N4 - R32
I am using the code shown below.
I enter details on my userform then pressing the command button sends those details to my worksheet & sorts column A-Z
A couple of times now it doesnt sort.
Ive just added a new customer for which is M HOUSE G + S.
It should of been sorted so its at N23 but its last at N32
Can you see an issue in the code.
My current worksheet where the details are stored is N4 - R32
Rich (BB code):
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
End With
.Cells(LastRow, "N").HorizontalAlignment = xlLeft
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(Rows.Count, 4).End(xlUp).Row
.Range("N4:R" & x).Sort Key1:=.Range("N4"), Order1:=xlAscending, Header:=xlGuess
.Range("N4").Select
End With
Unload Me
Application.ScreenUpdating = True
MsgBox "DATABASE SUCCESSFULLY UPDATED", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
End Sub