วิธีแก้ Error Excel

[snasui]

ไฟล์ที่แนบมาติดเรื่อง Do...Loop และ End With ช่วยใส่ให้ครบก่อนแล้วแนบมาใหม่ครับ

[Ueng999]

ลงปรับ Code ใหม่คะ ตอนนี้ค้นหาจาก Hn.ใช้งานได้ปกติคะ แต่ค้นหาจากชื่อ MsgBox แจ้งไม่พบข้มูล ช่วยดู code ให้ทีคะไม่ขึ้น error ได ๆ แต่เหมือน Code ไม่ทำงาน ไม่รู้ว่าผิดอะไร ตรงไหนคะ รบการชวยทีคะ

Code: Select all

Public Sub search_mode()
Dim id_search As String
Dim id_check As String
Dim data_row As Single
Dim i As Single
Dim rall As Range, r As Range
With Sheets("db_student")
    Set rall = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall
        If r.Value = .id_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
id_search = entry_form.id_txtbox.Value
data_row = 2
id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
With Worksheets(db_sheet)
Do Until id_check = id_search
    data_row = data_row + 1
    id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
    If Worksheets(db_sheet).Cells(data_row, id_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, id_col).End(xlUp).Row Then
        MsgBox "äÁ辺¢éÍÁÙÅ"
        Exit Sub
    End If
Loop
End With
With entry_form
.id_txtbox.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
.name_txtbox.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
.surname_txtbox.Value = Worksheets(db_sheet).Cells(data_row, surname_col).Value
.year_txtbox.Value = Worksheets(db_sheet).Cells(data_row, year_col).Value
.sequence_txtbox.Value = Worksheets(db_sheet).Cells(data_row, sequence_col).Value
.sex_cbbox.Value = Worksheets(db_sheet).Cells(data_row, sex_col).Value
.faculty_txtbox.Value = Worksheets(db_sheet).Cells(data_row, faculty_col).Value
.major_cbbox.Value = Worksheets(db_sheet).Cells(data_row, major_col).Value
End With
Dim name_search As String
Dim name_check As String
Dim data1_row As Single
Dim i1 As Single
Dim rall1 As Range, r1 As Range
With Sheets("db_student")
    Set rall1 = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall1
        If r.Value = .name_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
name_search = entry_form.name_txtbox.Value
data_row = 2
name_check = Worksheets(db_sheet).Cells(data_row, name_col).Value
With Worksheets(db_sheet)
Do Until name_check = name_search
    data_row = data_row + 1
  name_check = Worksheets(db_sheet).Cells(data_row, name_col).Value
    If Worksheets(db_sheet).Cells(data_row, name_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, name_col).End(xlUp).Row Then
        MsgBox "äÁ辺¢éÍÁÙÅ"
        Exit Sub
    End If
Loop
End With
With entry_form
.id_txtbox.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
.name_txtbox.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
.surname_txtbox.Value = Worksheets(db_sheet).Cells(data_row, surname_col).Value
.year_txtbox.Value = Worksheets(db_sheet).Cells(data_row, year_col).Value
.sequence_txtbox.Value = Worksheets(db_sheet).Cells(data_row, sequence_col).Value
.sex_cbbox.Value = Worksheets(db_sheet).Cells(data_row, sex_col).Value
.faculty_txtbox.Value = Worksheets(db_sheet).Cells(data_row, faculty_col).Value
.major_cbbox.Value = Worksheets(db_sheet).Cells(data_row, major_col).Value
End With
           End Sub

[Ueng999]

ผิดอันคะ

[Ueng999]

ขอโทษค่ะคิดว่าแนบ file ผิด file แนบถูดอันแล้วคะ

[snasui]

ตัวอย่างการปรับ Code ครับ

Code: Select all

'Other code
With entry_form
    For Each r In rall
        If r.Value = .id_txtbox.Text Or r.Offset(0, 1).Value = .name_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
'Other code