Snasui Excel Forum

Code: Select all

'Other code
lng1 = Application.CountIf(Rng1.Resize(, 1), Target)
If lng1 >= 1 Then ' ตรวจสอบข้อมูลเดิมว่าเคยขอหรือเปล่า
    For i = Rng1.Columns(1).Rows.Count To 1 Step -1
       If Rng1.Columns(1).Cells(i) = Target Then
            a = Rng1.Columns(1).Cells(i).Offset(0, 3).Value
            Exit For
       End If
    Next i
    MsgBox ("ลูกค้าเคยขอข้อมูลเมื่อวันที่  " & Format(a, "dd/mm/yyyy"))
 'Other code

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim rCheck As Range
    Dim ColName As Integer
    Dim ColNameE As Integer
    Dim DateB As Integer 'Çѹ·Õè¢ÍàÍ¡ÊÒÃ
    Dim Max As Integer

    
    Dim lng As Long
    Dim lng1 As Long
    
    If Target.Count > 1 Then Exit Sub
         If Target.Column = 3 Then
         
            TRow = Target.Row - 1
        
            With Sheets("AddData")
                Set rCheck = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            End With
        
            With Sheets("Data")
                Set Rng = .Range("A1", .Range("D" & Rows.Count).End(xlUp)) 'Range D ¤×Í Column ÊØ´·éÒ·Õè´Ö§
            End With
            
            With Sheets("AddData")
                Set Rng1 = .Range("C1", .Range("F" & Rows.Count).End(xlUp)) '´Ö§ Column C ¶Ö§ F
            End With
            
            With Sheets("AddData")
                Set Rng2 = .Range("C1:C" & TRow) '´Ö§ Column C ÁÒ 1 Column
            End With
            
            With Sheets("AddData")
                Set Rng3 = .Range("F1:F" & TRow) '´Ö§ Column F ÁÒ 1 Column
            End With
            
            With Sheets("AddData")
                Set Rng4 = .Range("E1:E" & TRow) '´Ö§ Column F ÁÒ 1 Column
            End With
            

            ColName = 2
            ColNameE = 3
            DateB = 4
            
                            
            lng = Application.CountIf(Rng.Resize(, 1), Target)
        
            If lng >= 1 Then
                Sheet11.Unprotect 1234
                If Target.Offset(0, 0) <> "" Then '¶éÒ Column äÁèà·èҡѺÇèÒ§ ãËé·Ó
                    lng1 = Application.CountIf(Rng1.Resize(, 1), Target)
                    If lng1 >= 1 Then ' µÃǨÊͺ¢éÍÁÙÅà´ÔÁÇèÒà¤Â¢ÍËÃ×Íà»ÅèÒ
                    
                    Range("L1").Formula = "=Lookup(2, 1 /(" & Rng2.Address & "=" & Target & ")," & Rng3.Address & ")"
                    FndVal = Range("L1").Value
                    Range("L1").Formula = "=Lookup(2, 1 /(" & Rng2.Address & "=" & Target & ")," & Rng4.Address & ")"
                    FndVal2 = Range("L1").Value
                    Range("L1").ClearContents
                    
                    
                        'MsgBox ("ÅÙ¡¤éÒà¤Â¢Í¢éÍÁÙÅàÁ×èÍÇѹ·Õè  " & Format(Application.VLookup(Target, Rng1, DateB, 0), "dd/mm/yyyy") & "  " & Application.VLookup(Target, Rng1, ColNameE, 0))
                        
                        MsgBox ("ÅÙ¡¤éÒà¤Â¢Í¢éÍÁÙÅàÁ×èÍÇѹ·Õè  " & WorksheetFunction.Text(FndVal, "dd/mm/yyyy") & "  " & FndVal2)
                        
                        Target.Offset(0, 2) = Application.VLookup(Target, Rng1, ColNameE, 0) 'àÍÒª×èÍÍѧ¡Äɢͧà¡èÒÁÒãÊè
                        Target.Offset(0, 3).Activate 'ä»·ÕèªèͧãÊèÇѹ·Õè¢éÍÁÙÅ·Õèµéͧ¡ÒÃ
                    End If
                    Target.Offset(0, 1) = Application.VLookup(Target, Rng, ColName, 0)
                    'Target.Offset(0, -1) = Now() 'Format(Now(), "dd/mm/yyyy") '¡Ó˹´ãËé Column B = Çѹ·Õè»Ñ¨¨ØºÑ¹
                    Target.Offset(0, -1) = Date
                    Target.Offset(0, -2) = Target.Offset(-1, -2) + 1 'ãËé Column A ÃѹàÅ¢ NO.
                    If Target.Offset(0, 2) = "" Then
                        Target.Offset(0, 2).Activate 'ãËéä»·Õè Column ¶Ñ´ä»
                    Else
                        Target.Offset(0, 3).Activate
                    End If
                Else
                    Target.Offset(0, -1) = ""
                    Target.Offset(0, -2) = ""
                    Target.Offset(0, 1) = ""
                    Target.Offset(0, 2) = ""
                    Target.Offset(0, 3) = ""
                    Target.Offset(0, 4) = ""
                    Target.Offset(0, 5) = ""
                    Target.Offset(0, 6) = ""
                    Target.Offset(0, 7) = ""
                    Target.Offset(0, 0).Activate
                End If
            Else
                '¶éÒäÁèÁÕã¹°Ò¹¢éÍÁÙÅ
                If Target.Offset(0, 0) <> "" Then
                    MsgBox ("àÅ¢·ÕèºÑ­ªÕ¹ÕéäÁèÁÕã¹°Ò¹¢éÍÁÙÅ")
                End If
                Sheet11.Unprotect 1234
                Target.Offset(0, -1) = ""
                Target.Offset(0, -2) = ""
                Target.Offset(0, 1) = ""
                Target.Offset(0, 2) = ""
                Target.Offset(0, 3) = ""
                Target.Offset(0, 4) = ""
                Target.Offset(0, 5) = ""
                Target.Offset(0, 6) = ""
                Target.Offset(0, 7) = ""
                Target.Offset(0, 0).Activate
                Sheet11.Protect 1234
            End If
        End If
        
        If Target.Column = 5 Then
            Target.Offset(0, 1).Activate
        End If
            
        If Target.Column = 6 Then
            Target.Offset(0, 1).Activate
        End If
            
        If Target.Column = 7 Then
            Target.Offset(0, 1).Activate
        End If
            
        If Target.Column = 8 Then
            Target.Offset(0, 1).Activate
        End If
            
        If Target.Column = 9 Then
            If Target.Offset(0, 0) <> "" Then
                If Target.Offset(0, 0) = 0 Then
                    Target.Offset(0, 1) = 0
                    Target.Offset(1, -6).Activate
                Else
                    Target.Offset(0, 1) = Target.Offset(0, -2) / Target.Offset(0, 0) 'Colume ·Õè7 à·èҡѺ Colume ·Õè 4 ËÒà Colume ·Õè6
                End If
            End If
        End If
    
        If Target.Column = 10 Then
            Target.Offset(1, -7).Activate
        End If
        
End Sub

Code: Select all

             For i = Rng1.Columns(1).Rows.Count To 1 Step -1
             If Rng1.Columns(1).Cells(i) = Target Then
            	 a = Rng1.Columns(1).Cells(i).Offset(0, 3).Value
             	 Exit For
             End If