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

T

Tayl4n

Board Regular
Joined
Feb 17, 2021
Messages
84
Office Version
  1. 2016
Platform
  1. Windows
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

:cool: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):

VBA Code: 
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
VBA Code: 
Set rEmpList = Sheets("Worksheet").Range("A:A")
Set rCodeList = Sheets("Worksheet").Range("G:G")
sNewName = Ucase(txtA.Value)
sNewCode = UCase(txtG.Value)
le = Application.WorksheetFunction.Match (sNewName, rEmpList,0)
bInsert = False
Do While Ucase(rEmpList(le)) = sNewName And bInsert = False
    If Ucase(rCodeList(le)) > sCodeName Then 
        Rows(le).Insert
        bInsert = True
    Else
        le=le+1
    End If
Loop
If bInsert = False then Rows(le).Insert
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
 
Upvote 0
Solution
mart37 said:
VBA Code: 
Set rEmpList = Sheets("Worksheet").Range("A:A")
Set rCodeList = Sheets("Worksheet").Range("G:G")
sNewName = Ucase(txtA.Value)
sNewCode = UCase(txtG.Value)
le = Application.WorksheetFunction.Match (sNewName, rEmpList,0)
bInsert = False
Do While Ucase(rEmpList(le)) = sNewName And bInsert = False
    If Ucase(rCodeList(le)) > sCodeName Then
        Rows(le).Insert
        bInsert = True
    Else
        le=le+1
    End If
Loop
If bInsert = False then Rows(le).Insert
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
Click to expand...
I edit as “Ucase” . it added 2 row
and added up to 5th row. I write “Bölüm” to A and “f49” to G
It added 4th row;
4-) “BÖLÜM” “f49”
5-) “Bölüm” “f48”
 
Upvote 0
mart37 said:
VBA Code: 
Set rEmpList = Sheets("Worksheet").Range("A:A")
Set rCodeList = Sheets("Worksheet").Range("G:G")
sNewName = Ucase(txtA.Value)
sNewCode = UCase(txtG.Value)
le = Application.WorksheetFunction.Match (sNewName, rEmpList,0)
bInsert = False
Do While Ucase(rEmpList(le)) = sNewName And bInsert = False
    If Ucase(rCodeList(le)) > sCodeName Then
        Rows(le).Insert
        bInsert = True
    Else
        le=le+1
    End If
Loop
If bInsert = False then Rows(le).Insert
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
Click to expand...
Sorry for my bad analysis this code doing this:
It add after 4th row as;
“BÖLÜM” “f49” (add datas with big letter also other columns)
And clear 5th row datas only left “Bölüm” and “a14”.
 
Upvote 0
Sorry this is the correct line: If UCase(rCodeList(le)) > sNewCode Then

To remove the big letters delete the function Ucase(). Example: sNewName = Ucase(txtA.Value) --> sNewName = txtA.Value
 
Upvote 0
mart37 said:
Sorry this is the correct line: If UCase(rCodeList(le)) > sNewCode Then

To remove the big letters delete the function Ucase(). Example: sNewName = Ucase(txtA.Value) --> sNewName = txtA.Value
Click to expand...
I changed “sCodeName” with “sNewCode”
I deleted Ucase() parts. (Letter problem solved)
But this time; it added a row after 6th row (correct place) only “Bölüm”
“f49” written other columns empty. And changed 7th row with entered data as; “Bölüm” “f49” and other columns filled with entered data(I mean h60 and updated as f49 and other columns updated which I entered data). It seems it add one column(after 6th row) with only two data (A and G columns), and update next column(7th row).
 
Upvote 0
These lines are an example:
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
You can delete them.

You have to add your own lines and change the row number.

'''''''''''''''''''''''Add data in excel sheet this part works clearly''''''''''''''''''''''''''''''''''''''
with sh
.Cells(le + 1, "A").Value = Me.txtA.Value --> .Cells(le, "A").Value = Me.txtA.Value
.Cells(le + 1, "B").Value = Me.txtB.Value --> .Cells(le, "B").Value = Me.txtB.Value
etc.
 
Upvote 0
These lines are an example:
mart37 said:
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
You can delete them.

You have to add your own lines and change the row number.

'''''''''''''''''''''''Add data in excel sheet this part works clearly''''''''''''''''''''''''''''''''''''''
with sh
.Cells(le + 1, "A").Value = Me.txtA.Value --> .Cells(le, "A").Value = Me.txtA.Value
.Cells(le + 1, "B").Value = Me.txtB.Value --> .Cells(le, "B").Value = Me.txtB.Value
etc.
Click to expand...
You are amazing! You made my day thank you so much!
 
Upvote 0
mart37 said:
Set rEmpList = Sheets("Worksheet").Range("A:A")
Set rCodeList = Sheets("Worksheet").Range("G:G")
sNewName = Ucase(txtA.Value)
sNewCode = UCase(txtG.Value)
le = Application.WorksheetFunction.Match (sNewName, rEmpList,0)
bInsert = False
Do While Ucase(rEmpList(le)) = sNewName And bInsert = False
If Ucase(rCodeList(le)) > sCodeName Then
Rows(le).Insert
bInsert = True
Else
le=le+1
End If
Loop
If bInsert = False then Rows(le).Insert
Range("A" & le ).Value = sNewName
Range("G" & le ).Value = sNewCode
[/CODE]
Click to expand...
Dear mart37,
Same function I need for Update button. I tried to adapt this codes for update but there is no results :(.
Can you help me also for this update button ?
Codes;

VBA Code: 
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

Sheets(“Worksheet”).Cells(y,1).Value = txtA
Sheets(“Worksheet”).Cells(y,2).Value = txtB
Sheets(“Worksheet”).Cells(y,3).Value = txtC
Sheets(“Worksheet”).Cells(y,4).Value = txtD
Sheets(“Worksheet”).Cells(y,5).Value = txtE
Sheets(“Worksheet”).Cells(y,6).Value = txtF
Sheets(“Worksheet”).Cells(y,7).Value = txtG
Sheets(“Worksheet”).Cells(y,8).Value = txtH
Sheets(“Worksheet”).Cells(y,9).Value = txtI
Sheets(“Worksheet”).Cells(y,10).Value = txtJ

Sheets(“Worksheet”).Cells(y,12).Value = txtL
Sheets(“Worksheet”).Cells(y,13).Value = txtM
Sheets(“Worksheet”).Cells(y,14).Value = txtN
Sheets(“Worksheet”).Cells(y,15).Value = txtO
Sheets(“Worksheet”).Cells(y,16).Value = txtP
Sheets(“Worksheet”).Cells(y,17).Value = txtQ
Sheets(“Worksheet”).Cells(y,18).Value = txtR
Sheets(“Worksheet”).Cells(y,19).Value = txtS
Sheets(“Worksheet”).Cells(y,20).Value = txtT
Sheets(“Worksheet”).Cells(y,21).Value = txtU
Sheets(“Worksheet”).Cells(y,22).Value = txtV
Sheets(“Worksheet”).Cells(y,23).Value = txtW
Sheets(“Worksheet”).Cells(y,24).Value = txtX
Sheets(“Worksheet”).Cells(y,25).Value = txtY
Sheets(“Worksheet”).Cells(y,26).Value = txtZ
Sheets(“Worksheet”).Cells(y,27).Value = txtAA
Sheets(“Worksheet”).Cells(y,28).Value = txtAB
Sheets(“Worksheet”).Cells(y,29).Value = txtAC
Sheets(“Worksheet”).Cells(y,30).Value = txtAD
Sheets(“Worksheet”).Cells(y,31).Value = txtAE
Sheets(“Worksheet”).Cells(y,32).Value = txtAF

End If
Next y

MsgBox “Updated”, vbInformation
End Sub
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA - Add column values to array if they meet a certain criterion
Replies
1
Views
7K
RickXL
RickXL
S
  • Question
VBA/Macro to add predefined value to a columns cells if two criteria are met
Replies
0
Views
452
supercell
S
A
  • Question
Need VBA to Duplicate Worksheet Template, Name It and Copy a Row of Values into Sections of One Column Based on One Column Information
Replies
0
Views
2K
Alejandro_R
A
G
  • Question
Add Column Values If Other Columns Match Criteria
Replies
2
Views
487
gmercer82
G

Forum statistics

Threads
1,214,789
Messages
6,121,593
Members
449,038
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top