Sort A-Z on a worksheet question

[ipbr21054]

Morning,
Just asking before I start.

I wish to have a user form where I send the values to the worksheet.

Lets assume the values will be inserted at F5:F12 then sorted A-Z in column F

My question is can it be sorted so the sort doesn’t touch the values that are in columns A5:E5

Or please advise otherwise.

 

[rollis13]

Yes, you can sort only the selected cells.
You can use the Recorder to create the macro. Start the Recorder, select range F5:F12, do your Sort and when it asks don't Expand the selection, Stop the Recoder. In the VBA you will then have the code to integrate in other macros.

 

[ipbr21054]

But will that interfer with the values on the same row for columns A-E

 

[ipbr21054]

Here is my code so far.

I enter the values to the userform.
I use the command button to enter the values to my worksheet in cells N4:R4

This works fine.

Now im stuck with sorting this list from A-Z using column N

Im not sure how to do it BUT i dont want to mess with any cell values in columns A-M when the sort takes place.

Please could you advise

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long

    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If
            
        With Sheets("G INCOME")
        .Range("N4").Value = TextBox1.Text
        .Range("O4").Value = TextBox2.Text
        .Range("P4").Value = TextBox3.Text
        .Range("Q4").Value = TextBox4.Text
        .Range("R4").Value = TextBox5.Text
        End With
        End With
        Range("N5").Select
        Unload AddCustomer
        MsgBox "SUCCESSFULLY NOW ADDED TO DATABASE", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End Sub

 

[ipbr21054]

My updated code with a sort code BUT it has an issue.
Having said that it doesnt mess with the other columns.

Please can you advise what is wrong with the code in RED

Some info for you.
N4 NAME
O4 ADDRESS
P4 POST CODE
Q4 CHARGE
R4 MILES

My current range at present with values are N4:R23

What i see happen with this code is the value is entered into the row 4
It then sorts the values A-Z
I then watch the first row be deleted / overwritten with the values below it

So basically my database is getting shorter

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long

    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If
            
        With Sheets("G INCOME")
        .Range("N4").Value = TextBox1.Text
        .Range("O4").Value = TextBox2.Text
        .Range("P4").Value = TextBox3.Text
        .Range("Q4").Value = TextBox4.Text
        .Range("R4").Value = TextBox5.Text
        
        End With
        
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 4).End(xlUp).Row
        .Range("N3:R" & x).Sort Key1:=.Range("N3"), Order1:=xlAscending, Header:=xlGuess
         End With

        
        Range("N5").Select
        Unload AddCustomer
        MsgBox "SUCCESSFULLY NOW ADDED TO DATABASE", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
    End Sub

 

[rollis13]

All I can see is that you are entering your data from textboxes in cells from N4 to R4 but then you are sorting from row 3 and down:
.Range("N3:R" & x).Sort Key1:=.Range("N3"), shouldn't it be from N4:R ?

 

[ipbr21054]

Hi,
I have had to ditch that idea as i come across a few issues.

The current code in use is shown below BUT the RED text is the issue.
It currently inserts to row 5 but if another value is there its overwritten.

So i think the best way forward is to find the last row with values, then enter values from userform, then sort A-Z
This way nothing is overwritten as its being inserted into the next available row which is empty each time.

Some info for you.
Current range N4:R4

Currently last row with the range with values is row 24
The code should find this then enter values from userform to Row 25
Then sort the new range A-Z using column N

Next time it will do the same but enter values to Row 26 etc etc
This is where im now stuck.

so userform to worksheet info

TextBox1 value to column N
TextBox2 value to column O
TextBox3 value to column P
TextBox4 value to column Q
TextBox5 value to column R

Private Sub CommandButton1_Click()
    Dim i As Long, x As Long
    
    With Sheets("G INCOME")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "NO ADDRESS WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf TextBox3.Value = "" Then
            MsgBox "NO POST CODE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox3.SetFocus
            Exit Sub
        ElseIf TextBox4.Value = "" Then
            MsgBox "NO CHARGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            TextBox4.SetFocus
            Exit Sub
        ElseIf TextBox5.Value = "" Then
            TextBox5.SetFocus
            MsgBox "NO MILEAGE WAS ENTERED", vbCritical, "NAME & ADDRESS EMPTY MESSAGE"
            Exit Sub
        End If
            
        With Sheets("G INCOME")
        .Range("N4").Value = TextBox1.Text
        .Range("O4").Value = TextBox2.Text
        .Range("P4").Value = TextBox3.Text
        .Range("Q4").Value = TextBox4.Text
        .Range("R4").Value = TextBox5.Text

        End With
        
        Range("N5").Select
        Unload AddCustomer
        MsgBox "SUCCESSFULLY NOW ADDED TO DATABASE", vbInformation, "GRASS INCOME NAME & ADDRESS MESSAGE"
        End With
        
    End Sub

 

[ipbr21054]

Trying to get this to find & insert the values in N:R but it keeps entering into A:e

Can you see / advise please.

        With ThisWorkbook.Worksheets("G INCOME")
        LastRow = Sheets("G INCOME").Cells(Rows.Count, 14).End(xlUp).Offset(1).Row
        
        .Cells(LastRow + 1, 1).Value = TextBox1.Text
        .Cells(LastRow + 1, 2).Value = TextBox2.Text
        .Cells(LastRow + 1, 3).Value = TextBox3.Text
        .Cells(LastRow + 1, 4).Value = TextBox4.Text
        .Cells(LastRow + 1, 5).Value = TextBox5.Text

        End With

 

[rollis13]

Change your snippet like this:

With ThisWorkbook.Worksheets("G INCOME")
    LastRow = .Cells(Rows.Count, 14).End(xlUp).Offset(1).Row
    .Cells(LastRow, 14).Value = TextBox1.Text
    .Cells(LastRow, 15).Value = TextBox2.Text
    .Cells(LastRow, 16).Value = TextBox3.Text
    .Cells(LastRow, 17).Value = TextBox4.Text
    .Cells(LastRow, 18).Value = TextBox5.Text
End With

 

[ipbr21054]

Many thanks for today,now completed