Match the value on A column first, then alphabetically add relative to G column

[Tayl4n]

I have a project on excel. I did add button but it need more functionality. When I add a new data this button must do this:

Match the value on A column (A value textbox=txtA) first, then alphabetically add relative to G column(G value textbox= txtG). (This is my logic. If there is another alternative logic I would like to hear that)

Example

___A_________B_C_D_E_F_______G______

1-)Müdür------ Blabla------ a10 (example values)

2-)Müdür------ Blabla------ c42

3-)Müdür------ Blabla------ d56

4-)Müdür------ Blabla------ e15

5-)Bölüm------ Blabla------ a14

6-)Bölüm------ Blabla------ f48

7-)Bölüm------ Blabla------ h60

Bölüm------ Blabla------ j72



Ex: When I enter A, txtA on textbox "Bölüm" and G,txtG on textbox "f49" or "g16" it must be added on after 6th row. Also other cells will be filled too but add function must insert new data's row depend only A and G column.

My codes (add function work clearly):

Private Sub cmdSave_Click()

Dim sh As Worksheet

Set sh = ThisWorkBook.Sheets("Worksheet")

Dim le As Long

lr = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row

'''''''''''''''''''''''''''''''''''''I worked this part for this function but function does not work clearly''''''''''''''''''''''''''''''''''''''''''''''

Dim sNewName As String

Dim rEmpList As Range

Set rEmpList = Sheets("Worksheet").Range("A:A")

sNewName = txtA.Value

le = Application.WorksheetFunction.Match (sNewName, rEmpList,1)

Rows(le+1).Insert

Range("G" & le + 1).Value = sNewName



'''''''''''''''''''''''Add data in excel sheet this part works clearly''''''''''''''''''''''''''''''''''''''

with sh

.Cells(le + 1, "A").Value = Me.txtA.Value

.Cells(le + 1, "B").Value = Me.txtB.Value

.Cells(le + 1, "C").Value = Me.txtC.Value

.Cells(le + 1, "D").Value = Me.txtD.Value

.Cells(le + 1, "E").Value = Me.txtE.Value

.Cells(le + 1, "F").Value = Me.txtF.Value

.Cells(le + 1, "G").Value = Me.txtG.Value

.Cells(le + 1, "H").Value = Me.txtH.Value

.Cells(le + 1, "I").Value = Me.txtI.Value

.Cells(le + 1, "J").Value = Me.txtJ.Value

.Cells(le + 1, "L").Value = Me.txtL.Value

.Cells(le + 1, "M").Value = Me.txtM.Value

.Cells(le + 1, "N").Value = Me.txtN.Value

.Cells(le + 1, "O").Value = Me.txtO.Value

.Cells(le + 1, "P").Value = Me.txtP.Value

Cells(le + 1, "Q").Value = Me.txtQ.Value

Cells(le + 1, "R").Value = Me.txtR.Value

Cells(le + 1, "S").Value = Me.txtS.Value

Cells(le + 1, "T").Value = Me.txtT.Value

Cells(le + 1, "U").Value = Me.txtU.Value

Cells(le + 1, "W").Value = Me.txtW.Value

Cells(le + 1, "X").Value = Me.txtX.Value

Cells(le + 1, "Y").Value = Me.txtY.Value

Cells(le + 1, "Z").Value = Me.txtZ.Value

Cells(le + 1, "AA").Value = Me.txtAA.Value

Cells(le + 1, "AB").Value = Me.txtAB.Value

Cells(le + 1, "AC").Value = Me.txtAC.Value

Cells(le + 1, "AD").Value = Me.txtAD.Value

Cells(le + 1, "AE").Value = Me.txtAE.Value

Cells(le + 1, "AF").Value = Me.txtAF.Value

Cells(le + 1, "K").Value = Me.txtK.Value

I didn't copy paste the codes. So if there is any word mistake please ignore this.

 

[Tayl4n]

This code is good for changing data.
With WorksheetFunction>Match you can find the row quicker.
What you need is I think a macro wich sort the data after changing. So column A and G are in the right order.

Yes I think about the sort but this is the problem: “Bölüm” may change to “Müdür” or other categories (there are almost 10 different category and near 500 rows). We do many updates and each update I have to carry rows correct category manuelly. After that I should sort them again and again. It is hard do this every time.

 

[mart37]

Sub sort()
    Dim sh As Worksheet
    Dim rData As Range
    Dim lr As Long
    Dim ic As Integer
    Set sh = ThisWorkbook.Sheets("Worksheet")
    With sh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        ic = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rData = .Range(Cells(1, 1), Cells(lr, ic))
        .sort.SortFields.Clear
        .sort.SortFields.Add2 Key:=Range("A1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .sort.SortFields.Add2 Key:=Range("G1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .sort
            .SetRange rData
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

 

[Tayl4n]

Sub sort()
    Dim sh As Worksheet
    Dim rData As Range
    Dim lr As Long
    Dim ic As Integer
    Set sh = ThisWorkbook.Sheets("Worksheet")
    With sh
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        ic = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rData = .Range(Cells(1, 1), Cells(lr, ic))
        .sort.SortFields.Clear
        .sort.SortFields.Add2 Key:=Range("A1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .sort.SortFields.Add2 Key:=Range("G1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .sort
            .SetRange rData
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

These codes are not working. Where should I add these ?

 

[mart37]

In a module. Example: Module1
In your own macro's you can use SORT to order the data.
Example:

Private Sub cmdUpdate_Click()
Dim x As Long
Dim y As Long
x= Sheets(“Worksheet”).Range(“K” & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets (“Worksheet”).Cells(y,11).Value = txtK.Text Then
Sheets(“Worksheet”).Cells(y,11).Value = txtK
.
.
.
End If
Next y
Call MODULE1.Sort
MsgBox “Updated”, vbInformation
End Sub

 

[Tayl4n]

In a module. Example: Module1
In your own macro's you can use SORT to order the data.
Example:

Private Sub cmdUpdate_Click()
Dim x As Long
Dim y As Long
x= Sheets(“Worksheet”).Range(“K” & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets (“Worksheet”).Cells(y,11).Value = txtK.Text Then
Sheets(“Worksheet”).Cells(y,11).Value = txtK
.
.
.
End If
Next y
Call MODULE1.Sort
MsgBox “Updated”, vbInformation
End Sub

Run-time error ‘438’:
Object doesn’t support this property or method

I clicked debug and it shows up:

      .sort.SortFields.Add2 Key:=Range("A1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Fluff]

Try changing the name of the sub, you should never use VBA keywords (such as Sort) for the names of procedures or variables.

 

[Tayl4n]

Try changing the name of the sub, you should never use VBA keywords (such as Sort) for the names of procedures or variables.

I changed with a spesific word(“sira”) Same error same lines .

 

[mart37]

.sort.SortFields.Add Key:=Range("A1") , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Tayl4n]

.sort.SortFields.Add Key:=Range("A1") , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

The function is work now! But all category group places are changing alphabeticaly this time . We don’t want this because ex. Müdür’s group rows must higher than Bölüm’s rows. “Müdür” means “Manager” and “Bölüm” means “Departman” there is hierarchical order first. Then each group has sorting alphabeticaly up to a and g columns in within itself. When I click update updated data gets in correct group but “Bölüm” rows getting higher than “Müdür” rows.

 

[Tayl4n]

.sort.SortFields.Add Key:=Range("A1") , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

rows.

___A_________B_C_D_E_F_______G______

1-)Müdür------ Blabla------ a10 (example values)

2-)Müdür------ Blabla------ c42

3-)Müdür------ Blabla------ d56

4-)Müdür------ Blabla------ e15

5-)Bölüm------ Blabla------ a14

6-)Bölüm------ Blabla------ f48

7-)Bölüm------ Blabla------ h60

8–)Bölüm------ Blabla------ j72

Ex. when I cahange 3rd row “Müdür” to “Bölüm” and uptade
This row must go after to 5th row