Excel Forum

Code: Select all

Sub ตรวจสอบข้อมูล()
    Dim wsMain As Worksheet
    Dim wsSection As Worksheet
    Dim mainValue As String
    Dim sectionValue As String
    Dim foundRow As Long
    Dim matchFound As Boolean
    Dim i As Long
    Dim j As Long
    
    ' ค้นจากชีทMain และ Section
    Set wsMain = ThisWorkbook.Sheets("Main")
    Set wsSection = ThisWorkbook.Sheets("Section")
    
    ' เอาข้อมูลจากช่องJ1
    mainValue = wsMain.Range("J1").Value
    
    matchFound = False
    For i = 3 To wsSection.Cells(wsSection.Rows.Count, "A").End(xlUp).Row
        If wsSection.Cells(i, 1).Value = mainValue Then
            foundRow = i
            matchFound = True
            
            For j = 7 To wsMain.Cells(wsMain.Rows.Count, "B").End(xlUp).Row
                If wsSection.Cells(foundRow, 6).Value = wsMain.Cells(j, 2).Value Then
                    MsgBox "ข้อมูลตรงกัน"
                    Exit Sub
                End If
            Next j
        End If
    Next i
    If Not matchFound Then
        MsgBox "ไม่พบข้อมูลที่ตรงกัน"
    End If
End Sub

Code: Select all

Sub CheckAcces()
    Dim wsMain As Worksheet
    Dim wsSection As Worksheet
    Dim mainValue As String
    Dim sectionValue As String
    Dim foundRow As Long
    Dim matchFound As Boolean
    Dim i As Long
    Dim j As Long
    
    ' ¤é¹¨Ò¡ªÕ·Main áÅÐ Section
    Set wsMain = ThisWorkbook.Sheets("Main")
    Set wsSection = ThisWorkbook.Sheets("Section")
    
    ' àÍÒ¢éÍÁÙŨҡªèͧJ1
    mainValue = wsMain.Range("J1").Value
    
    matchFound = False
    For i = 3 To wsSection.Cells(wsSection.Rows.Count, "A").End(xlUp).Row
        If wsSection.Cells(i, 1).Value = mainValue Then
            foundRow = i
            matchFound = True
            Exit For
        End If
    Next i

    If Not matchFound Then
        MsgBox "Not found"
    End If
    
    For j = foundRow To wsSection.Cells(wsSection.Rows.Count, "a").End(xlUp).Row
        If Application.CountIfs(wsMain.Range("b7", wsMain.Range("b" & wsMain.Rows.Count).End(xlUp)) _
            , wsSection.Cells(j, 1).Value) Then
            MsgBox "Found " & wsSection.Cells(j, 1).Value & " in  sheet Main", vbInformation
        Else
            MsgBox "Not found " & wsSection.Cells(j, 1).Value & " in sheet Main", vbExclamation
        End If
    Next j