Snasui Excel Forum

Code: Select all

Private Sub TextBox2_text()
Application.IndexApplication.WorksheetFunction.Index
Set LastRow = TextBox1.Text
           LastRow.Index(Range(Data), Match(TextBox1.Text, Range(Name), 0), 2) = TextBox2.Text
End Sub

Private Sub TextBox3_value()
Application.IndexApplication.WorksheetFunction.Index
Set LastRow = TextBox1.Value
           LastRow.Index(Range(Data), Match(TextBox1.Value, Range(Name), 0), 3) = TextBox3.Value
End Sub


Private Sub TextBox2_Change()
Set LastRow = Sheet1.Range("a65536").End(xlUp)
Application.IndexApplication.WorksheetFunction.Index
Set LastRow = TextBox1.Text
           LastRow.Index(Range(Data), Match(TextBox1.Text, Range(Name), 0), 2) = TextBox2.Text
End Sub

Private Sub TextBox2_value()
Set LastRow = Sheet1.Range("a65536").End(xlUp)
Application.IndexApplication.WorksheetFunction.Index_
Application.IndexApplication.WorksheetFunction.Match
Set LastRow = TextBox1.Value
           LastRow.Index(Range(Data), Match(TextBox1.Value, Range(Name), 0), 2) = TextBox2.Text
End Sub

Code: Select all

'Other code
With Application
	TextBox2.Text = .Index(Data,.Match(LastRow,Name,0),2)
End With
'Other code

Code: Select all

Private Sub TextBox2_Change()
Application.IndexApplication.WorksheetFunction.Index
    Set LastRow = TextBox1.Text
With Application
     TextBox2.Text = .Index(Data, .Match(LastRow, Name, 0), 2)
End With
End Sub

Code: Select all

'Other code
Dim Data As Range
Dim Name As Range
Set Data = ActiveSheet.Range("Data")
Set Name = ActiveSheet.Range("Name")
LastRow = TextBox1.Text
With Application
     TextBox2.Text = .Index(Data, .Match(LastRow, Name, 0), 2)
End With
'Other code

Code: Select all

Private Sub TextBox2_Change()
    Dim Data As Range
Dim Name As Range
Set Data = ActiveSheet.Range("Data")
Set Name = ActiveSheet.Range("Name")
LastRow = TextBox1.Text

     TextBox2.Text = .Index(Data, .Match(LastRow, Name, 0), 2)
End With
End Sub

Code: Select all

Private Sub TextBox1_Change()
    Dim Data As Range
    Dim Name As Range
    Dim lastRow As Integer
    Dim checkVal As Long
    Set Data = ActiveSheet.Range("Data")
    Set Name = ActiveSheet.Range("Name")
    With Application
        checkVal = .CountIf(Name, TextBox1.Text)
        If checkVal > 0 Then
            lastRow = .Match(TextBox1.Text, Name, 0)
            TextBox2.Text = .Index(Data, lastRow, 2)
        Else
            TextBox2.Text = ""
        End If
    End With
End Sub

Code: Select all

Private Sub ComboBox1_Change()
    Dim Num As Range 'Num:rangename Column เลขที่
    Dim Alldata As Range 'AllData:rangename ข้อมูลทั้งหมด(ทุกColumn)
    Dim lastRow As Integer
    Dim checkVal As Long
    Set Alldata = ActiveSheet.Range("AllData")
    Set Num = ActiveSheet.Range("Num")
    With Application
        checkVal = .CountIf(Num, ComboBox1.Text)
        If checkVal > 0 Then
            lastRow = .Match(CInt(ComboBox1.Text), Num, 0)
            TextBox1.Text = .Index(Alldata, lastRow, 1)
            TextBox2.Text = .Index(Alldata, lastRow, 2)
            TextBox3.Text = .Index(Alldata, lastRow, 3)
            TextBox4.Text = .Index(Alldata, lastRow, 4)
            TextBox5.Text = .Index(Alldata, lastRow, 5)
        Else
            ComboBox1.Text = ""
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
            TextBox4.Text = ""
            TextBox5.Text = ""
        End If
    End With
End Sub

Code: Select all

'Other code
If ComboBox1.Text <> "" And checkVal > 0 Then
'Other code

Code: Select all

Private Sub ComboBox1_Change()
    Dim Num As Range 'Num:rangename Column 
    Dim Alldata As Range 'AllData:rangename 
    Dim lastRow As Integer
    Dim checkVal As Long
    Set Alldata = ActiveSheet.Range("AllData")
    Set Num = ActiveSheet.Range("Num")
    With Application
        checkVal = .CountIf(Num, ComboBox1.Text)
        If ComboBox1.Text <> "" And checkVal > 0 Then
            lastRow = .Match(CInt(ComboBox1.Text), Num, 0)
            TextBox1.Text = .Index(Alldata, lastRow, 2)
            TextBox2.Text = .Index(Alldata, lastRow, 3)
            TextBox3.Text = .Index(Alldata, lastRow, 4)
            TextBox4.Text = .Index(Alldata, lastRow, 5)
            TextBox5.Text = .Index(Alldata, lastRow, 6)
        Else
            ComboBox1.Text = ""
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
            TextBox4.Text = ""
            TextBox5.Text = ""
        End If
    End With
    ComboBox1.SetFocus
End Sub