Snasui Excel Forum

Posted: **Thu May 22, 2014 8:26 am**

สวัสดีครับอาจารย์ผมมีปัญหาสอบถามอาจารย์เรื่องการสอบถามข้อมูลลูกค้าในชีทงาน..
คีย์เงื่อนไขแล้วข้อมูลไม่แสดงข้อมูลครับ..
Option Explicit
Private Sub CommandButton1_Click()

Dim customer_data As String
Dim data_row As Single
Dim i As Single
Const db_sheet = "db_customers"
Const id_col = 1
Const sub_col = 2
Const dept_col = 3
Const name_col = 4
Const type_col = 5
Const account1_col = 6
Const account2_col = 7
Const account3_col = 8
Const account4_col = 9
Const account5_col = 10
Const account6_col = 11

With Worksheets(db_sheet)
data_row = 2
'set matching data
If TextBox1.Text = Worksheets(db_sheet).Cells(data_row, id_col).Value And TextBox2.Text = Worksheets(db_sheet).Cells(data_row, sub_col).Value And TextBox3.Text = Worksheets(db_sheet).Cells(data_row, dept_col).Value Then
data_row = data_row + 2
customer_data = "True"
End If

End With
'if id ,sub and dept match, data show
If customer_data = "True" Then

With UserForm1
.TextBox4.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
.TextBox5.Value = Worksheets(db_sheet).Cells(data_row, type_col).Value
.TextBox6.Value = Worksheets(db_sheet).Cells(data_row, account1_col).Value
.TextBox7.Value = Worksheets(db_sheet).Cells(data_row, account2_col).Value
.TextBox8.Value = Worksheets(db_sheet).Cells(data_row, account3_col).Value
.TextBox9.Value = Worksheets(db_sheet).Cells(data_row, account4_col).Value
.TextBox10.Value = Worksheets(db_sheet).Cells(data_row, account5_col).Value
.TextBox11.Value = Worksheets(db_sheet).Cells(data_row, account6_col).Value
.TextBox12.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
.TextBox13.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value

End With
'¶éÒ¢éÍÁÙÅäÁèµÃ§¡Ñºà§×èÍ¹ä¢á¨é§àµ×Í¹
Else
MsgBox ("äÁè¾º¢éÍÁÙÅ¹Õé...¡ÃØ³ÒµÃÇ¨ÊÍºÍÕ¡¤ÃÑé§")
'clear both TextBox
TextBox1.Text = vbNullString
TextBox2.Text = vbNullString
TextBox3.Text = vbNullString
TextBox4.Text = vbNullString
TextBox5.Text = vbNullString
TextBox6.Text = vbNullString
TextBox7.Text = vbNullString
TextBox8.Text = vbNullString
TextBox9.Text = vbNullString
TextBox10.Text = vbNullString
TextBox11.Text = vbNullString
TextBox12.Text = vbNullString
TextBox13.Text = vbNullString
'set focus to TextBox1
TextBox1.SetFocus
End If

End Sub
ขอบคุณอาจารย์ล่วงหน้านะครับ

Posted: **Thu May 22, 2014 9:57 am**

ลองปรับ Code เป็นด้านล่างครับ

Code: Select all

'other code
'set matching data
Do While .Cells(data_row, id_col) <> ""
    If TextBox1.Text = Worksheets(db_sheet).Cells(data_row, id_col).Value And TextBox2.Text = Worksheets(db_sheet).Cells(data_row, sub_col).Value And TextBox3.Text = Worksheets(db_sheet).Cells(data_row, dept_col).Value Then
        customer_data = "True"
        Exit Do
    End If
    data_row = data_row + 1
Loop
'other code

Posted: **Fri May 30, 2014 11:10 am**

ขอบคุณอาจารย์มากๆครับผม..ทำได้แล้วครับ..

Snasui Excel Forum

ผมสอบถามข้อมูลลูกค้าในแถวถัดไปไม่ได้ครับ

ผมสอบถามข้อมูลลูกค้าในแถวถัดไปไม่ได้ครับ

Re: ผมสอบถามข้อมูลลูกค้าในแถวถัดไปไม่ได้ครับ

Re: ผมสอบถามข้อมูลลูกค้าในแถวถัดไปไม่ได้ครับ