Snasui Excel Forum

Code: Select all

Sub ShowEmp()
    Application.EnableEvents = False
    Dim rSource As Range, rTarget As Range
    Dim r As Range, i As Integer
    With Sheets("Postdata")
        If .Range("F2") <> "" Then
            Set rSource = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
        Else
            Set rSource = Range("F2")
        End If
    End With
    i = 1
    Sheets("ทะเบียน").Range("A6", Range("F" & Rows.Count)).ClearContents
    For Each r In rSource
        If r = Sheets("ทะเบียน").Range("C2") Then
            Set rTarget = Sheets("ทะเบียน").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            rTarget.Resize(1, 5) = r.Offset(0, -5).Resize(1, 5).Value
            rTarget.Offset(0, -1) = i
            i = i + 1
        End If
    Next r
    Application.EnableEvents = True
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Count = 1 Then
        If Target.Address = "$C$2" And Target <> "" Then
            ShowEmp
        ElseIf Target.Address = "$C$2" And Target = "" Then
            MsgBox "Please select data."
        End If
    End If
    Application.EnableEvents = True
End Sub