mardi 5 juin 2018

vba compare two sheets to validate values

I would like to make code that if column B and A values are in the CheckList sheet, Column C,D,E values must be in CheckList sheet. However validating Column B and A works good, but C, D, E are not working correctly.

Does anyone have any idea on this?

Private Function rowLast()
    rowLast = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
End Function

Dim rngCell As Range
Dim ValidateL As Long: ValidateL = Sheets("CheckList").Cells(Rows.Count, 1).End(xlUp).Row
For Each rngCell In ActiveSheet.Range("F13:F" & rowLast)
      If WorksheetFunction.CountIf(Sheets("CheckList").Range("B2:B" & ValidateL), rngCell) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("A2:A" & ValidateL), rngCell.Offset(, -2)) <> 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("C2:C" & ValidateL), rngCell.Offset(, -1)) = 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("D2:D" & ValidateL), rngCell.Offset(, 2)) = 0 And _
      WorksheetFunction.CountIf(Sheets("CheckList").Range("E2:E" & ValidateL), rngCell.Offset(, 1)) = 0 Then
       MsgBox "Please validate this combo " & rngCell & " Row Num: " & rngCell.Row
       End
    End If

Next
End Sub

Aucun commentaire:

Enregistrer un commentaire