ต้องการบันทึกข้อมูลให้อยู่ในแถวเดิม

[chaiwat chinthao]

หาข้อมูลจากเลขที่ประเมินใน Sheet "db_Incomming Cars All"ด้วยปุ่ม"ค้นหา" เพื่อแก้ไขข้อมูลและบันทึกกลับแถวเดิมใน Sheet "db_Incomming Cars All"ด้วยปุ่ม "อัพเดทข้อมูล"
*ติดปัญหาคือ.อัพเดทข้อมูล.แล้วข้อมูลไปต่อแถวสุดท้ายไม่ทับแถวเดิมครับ
แนบ Code

Code: Select all

Private Sub CommandButton3_Click()
 Dim irow As Long
    Dim ws As Worksheet
    On Error Resume Next
    If Err.Number = 13 Then
MsgBox "ไม่พบข้อมูล"
GoTo iNet
    End If
    If TextBox1.Text = "" Then
MsgBox "กรุณากรอกข้อมูล"
GoTo iNet
 End If
            Set ws = Worksheets("db_Incomming Cars All")
      irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
       If Application.CountIf(ws.Range("b:b"), TextBox1.Value) > 0 Then
        irow = Application.Match(TextBox1.Value, ws.Range("b:b"), 0)
Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    
        ws.Cells(irow, 2) = TextBox1.Value
        ws.Cells(irow, 3) = TextBox2.Value
        ws.Cells(irow, 4) = TextBox3.Value
        ws.Cells(irow, 5) = TextBox4.Value
        ws.Cells(irow, 6) = TextBox5.Value
        ws.Cells(irow, 7) = TextBox6.Value
        ws.Cells(irow, 8) = TextBox7.Value
        ws.Cells(irow, 9) = TextBox8.Value
        ws.Cells(irow, 10) = TextBox9.Value
        ws.Cells(irow, 11) = TextBox10.Value
        ws.Cells(irow, 12) = TextBox11.Value
        ws.Cells(irow, 13) = TextBox12.Value
        ws.Cells(irow, 14) = TextBox13.Value
        ws.Cells(irow, 15) = TextBox14.Value
        ws.Cells(irow, 16) = TextBox15.Value
        ws.Cells(irow, 17) = TextBox16.Value
        ws.Cells(irow, 18) = TextBox17.Value
        ws.Cells(irow, 19) = TextBox18.Value
        ws.Cells(irow, 20) = TextBox19.Value
        ws.Cells(irow, 21) = TextBox20.Value
        ws.Cells(irow, 22) = TextBox21.Value
        
        TextBox1.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
TextBox11.Text = " "
TextBox12.Text = " "
TextBox13.Text = " "
TextBox14.Text = " "
TextBox16.Text = " "
TextBox15.Text = " "
TextBox16.Text = " "
TextBox17.Text = " "
TextBox18.Text = " "
TextBox19.Text = " "
TextBox20.Text = " "
TextBox21.Text = " "
        
        MsgBox "อัพเดทข้อมูลแล้ว"
        ActiveWorkbook.Save
iNet:
End Sub

[snasui]

การโพสต์ Code ให้ดูได้ที่กฎการใช้บอร์ดข้อ 5 ด้านบนจะมีตัวอย่าง ตามโพสต์ #1 ผมปรับให้แสดงเป็น Code ให้แล้วครับ

สำหรับการปรับ Code ดูตัวอย่างตามด้านล่างครับ

Code: Select all

'Other code
Set ws = Worksheets("db_Incomming Cars All")
'    irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

If Application.CountIf(ws.Range("b:b"), TextBox1.Value) > 0 Then
    irow = Application.Match(CDbl(TextBox1.Value), ws.Range("b:b"), 0)
Else
    irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If
'Other code

[chaiwat chinthao]

สอบถามเพิ่มเติมครับ ผมใช้CommandButton2ในการค้นหาข้อมูลจาก TextBox1 และTextBox4 ติดปัญหาคือผมค้นหาข้อมูลจาก TextBox4 ได้แค่ครั้งเดียว ถ้าผมจะค้นหาข้อมูลจาก TextBox4 อีก ต้องปิด Usre Form แล้วเปิดใหม่ ผมต้องการค้นหาได้ต่อเนื่องโดยไม่ต้องปิด UserForm ครับ

Code: Select all

Private Sub CommandButton2_Click()
Dim nRow As String
On Error Resume Next
'Err.Clear
If TextBox1.Text <> "" Then
eRow = Worksheets("db_Incomming Cars All").Columns(2).Find(TextBox1.Text).Row
nRow = eRow
 ElseIf TextBox4.Text <> "" Then
 fRow = Worksheets("db_Incomming Cars All").Columns(5).Find(TextBox4.Text).Row
nRow = fRow
Else
MsgBox "ใส่ข้อมูลช่องเลขที่ประเมินหรือเลขทะเบียน"
GoTo nNext
  End If

If Err.Number = 91 Then
TextBox1.Text = " "
TextBox2.Text = " "
TextBox3.Text = " "
TextBox4.Text = " "
TextBox5.Text = " "
TextBox6.Text = " "
TextBox7.Text = " "
TextBox8.Text = " "
TextBox9.Text = " "
TextBox10.Text = " "
TextBox11.Text = " "
TextBox12.Text = " "
TextBox13.Text = " "
TextBox14.Text = " "
TextBox16.Text = " "
TextBox15.Text = " "
TextBox16.Text = " "
TextBox17.Text = " "
TextBox18.Text = " "
TextBox19.Text = " "
TextBox20.Text = " "
TextBox21.Text = " "
MsgBox "ไม่พบข้อมูล"
GoTo nNext
End If
 
TextBox1.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 2)
TextBox2.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 3)
TextBox3.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 4)
TextBox4.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 5)
TextBox5.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 6)
TextBox6.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 7)
TextBox7.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 8)
TextBox8.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 9)
TextBox9.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 10)
TextBox10.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 11)
TextBox11.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 12)
TextBox12.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 13)
TextBox13.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 14)
TextBox14.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 15)
TextBox15.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 16)
TextBox16.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 17)
TextBox17.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 18)
TextBox18.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 19)
TextBox19.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 20)
TextBox20.Value = Worksheets("db_Incomming Cars All").Cells(nRow, 21)
Sheets("db_Incomming Cars All").Cells(nRow, 1).Activate
nNext:
End Sub

แนบไฟล์ครับ ขอบคุณครับ

[snasui]

เพิ่มการประกาศตัวแปร dflt ไว้บนสุดของ Module

Code: Select all

Dim dflt As String

เพิ่ม Procedure 2 ชุดตามด้านล่าง

Code: Select all

Private Sub TextBox4_AfterUpdate()
    dflt = "Tb4"
End Sub

Private Sub TextBox1_AfterUpdate()
    dflt = "Tb1"
End Sub

แก้ไข Code สำหรับการค้นหาเป็นดานล่าง

Code: Select all

'Other code
If dflt = "Tb1" Then
    eRow = Worksheets("db_Incomming Cars All").Columns(2).Find(TextBox1.Text).Row
    nRow = eRow
ElseIf dflt = "Tb4" Then
    fRow = Worksheets("db_Incomming Cars All").Columns(5).Find(TextBox4.Text).Row
    nRow = fRow
Else
'Other code

[chaiwat chinthao]

ขอบคุณครับ