Selected Answer
Alaa
Your line
Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
locates the first instance of whatever is in your ComboBox1 of UserForm1. So for Summir, it would find cell B2.
The code below add the parameter searchdirection:=xlPrevious to that Rng Find line (so it locates the last instance instead)
To insert a line there (with formatting like the other data rows,) use the code below (changes in bold):
Private Sub CommandButton1_Click()
Dim ws As Worksheet, Rng As Range, Sel As Variant
Set ws = Sheets("sheet1")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole, searchdirection:=xlPrevious) 'search backawrds!
If Not Rng Is Nothing Then
Cells(Rng.Row + 1, "A").Resize(1, 6).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
ws.Cells(Rng.Row + 1, "A") = Now
ws.Cells(Rng.Row + 1, "B") = Sel
ws.Cells(Rng.Row + 1, "C") = Me.TextBox1.Value
ws.Cells(Rng.Row + 1, "D") = Me.TextBox2.Value
ws.Cells(Rng.Row + 1, "E") = Me.TextBox3.Value
ws.Cells(Rng.Row + 1, "F") = Me.TextBox4.Value
End If
End If
Unload Me
End Sub
Notice that the first full bold line (over two lines) inserts a row below.
Note: having command buttons with the same name (like CommandButton1) is confusing and should be avoided. You can edit them in the Properties. Normally you'd finish the macro (before End Sub) with
Unload Me
This makes the form disappear and claers the memory it uses. You can add a macro to your button on the worksheet with
UserForm1.Show
Also, I'd suggest you avoid putting your ComboBox RowSource (your named range list) on another sheet so it can't be altrered by manipulation of data rows (I had to use that Range.Insert method to avoid addding a row to it!
Hope this helps