vba nested if

[jaik22]

Hi,

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?

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

Thanks!

 

[davesexcel]

Is this the complete code?

 

[jaik22]

yes this is the complete code. Forgot to add variable declare and

Dim rngCell As Range

add

End Sub

at the end. though

 

[davesexcel]

Okay, I was just trying out the code,
You have no dim for or set variable for rngcell or rowlast

 

[jaik22]

Okay, I was just trying out the code,
You have no dim for or set variable for rngcell or rowlast

my bad rowlast is a private function.

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

Thanks for the support!