VBA to verify data in multiple columns

[Godwin117]

I have this VBA, it checks to ensure the correct info is put into the cell, it uses column "E", based on that input, verifies that the correct information was put into column "H" The issue I'm running into is it either doesn't recognize the column is blank or it doesn't go per row when it is verifying. The VBA is below.

Sub Verify_Data()
Application.ScreenUpdating = False
On Error Resume Next

Dim iAE As Variant, iAH As Variant, iR&, lR&

dtext = "PDY"
lR = Cells(Rows.Count, 1).End(xlUp).Row
iAE = Range("E12:E" & lR).Value
iAH = Range("H12:H" & lR).Value
For iR = 1 To UBound(iAE)
If iAE(iR, 1) = dtext Then
If iAH(iR, 1) <> "BMM" Or "DEP" Or "FTX" Or "QUARTERS" Or "RECOVERY" Or "" Then
mt = "Status Error"
mp = "Need to check Date in H" & iR& + 11
m = MsgBox(mp, vbOKOnly, mt)
Exit Sub
End If

End If

Next
End Sub

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Sub Verify_Data()
    Dim iAE As Variant, iAH As Variant, m As Variant
    Dim iR As Long, lR As Long
    Dim mp As String
    
    dtext = "PDY"
    
    lR = Cells(Rows.Count, 5).End(xlUp).Row
    iAE = Range("E12:E" & lR).Value
    iAH = Range("H12:H" & lR).Value
    
    For iR = 1 To UBound(iAE)
        If iAE(iR, 1) = dtext Then
            m = Application.Match(iAH(iR, 1), Array("BMM", "DEP", "FTX", "QUARTERS", "RECOVERY"), 0)
            If IsError(m) Then
                mp = "Need to check Date in H" & iR + 11
                MsgBox mp, vbOKOnly, "Status Error"
            Exit Sub
        End If
            End If
    Next iR
End Sub

Dave

 

[Godwin117]

Hi,
try this update to your code & see if does what you want

Sub Verify_Data()
    Dim iAE As Variant, iAH As Variant, m As Variant
    Dim iR As Long, lR As Long
    Dim mp As String
   
    dtext = "PDY"
   
    lR = Cells(Rows.Count, 5).End(xlUp).Row
    iAE = Range("E12:E" & lR).Value
    iAH = Range("H12:H" & lR).Value
   
    For iR = 1 To UBound(iAE)
        If iAE(iR, 1) = dtext Then
            m = Application.Match(iAH(iR, 1), Array("BMM", "DEP", "FTX", "QUARTERS", "RECOVERY"), 0)
            If IsError(m) Then
                mp = "Need to check Date in H" & iR + 11
                MsgBox mp, vbOKOnly, "Status Error"
            Exit Sub
        End If
            End If
    Next iR
End Sub

Dave

Thank you for your response, I tried the code and it doesnt skip the blanks, not sure if that's an option.

 

[dmt32]

Thank you for your response, I tried the code and it doesnt skip the blanks, not sure if that's an option.

I overlooked that

try

Sub Verify_Data()
    Dim iAE As Variant, iAH As Variant, m As Variant
    Dim iR As Long, lR As Long
    Dim mp As String
    
    dtext = "PDY"
    
    lR = Cells(Rows.Count, 5).End(xlUp).Row
    iAE = Range("E12:E" & lR).Value
    iAH = Range("H12:H" & lR).Value
    
    For iR = 1 To UBound(iAE)
        If iAE(iR, 1) = dtext And Len(iAH(iR, 1)) > 0 Then
            m = Application.Match(iAH(iR, 1), Array("BMM", "DEP", "FTX", "QUARTERS", "RECOVERY"), 0)
            If IsError(m) Then
                mp = "Need to check Date in H" & iR + 11
                MsgBox mp, vbOKOnly, "Status Error"
            Exit Sub
        End If
            End If
    Next iR
End Sub

Dave