Need to run a check before updating spreadsheet

[kevingcp]

I need to run a check where if column B = 0890-fed or 0890-DBW that column C must be between 10100 and 10199

AND if column B = 0995, column C must between 17000 and 17999...if the conditions fail, a message will pop up with an error.

Thank you!



Tracking Log.JPG

[nigelbloomy]

Does this do what you want?

Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To LastRow
    If Cells(i, 2) = "0890-fed" Or Cells(i, 2) = "0890-DBW" Then
        If Not Cells(i, 3) <= 10199 And Cells(i, 3) >= 10100 Then
            GoTo errormsg
        End If
    End If

    If Cells(i, 2) = 995 Then
        If Not Cells(i, 3) <= 17999 And Cells(i, 3) >= 17000 Then
            GoTo errormsg
        End If
    End If
Next i
Exit Sub
errormsg:
MsgBox "You have problems", vbOKOnly

End Sub

[kevingcp]

Does this do what you want?

Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To LastRow
    If Cells(i, 2) = "0890-fed" Or Cells(i, 2) = "0890-DBW" Then
        If Not Cells(i, 3) <= 10199 And Cells(i, 3) >= 10100 Then
            GoTo errormsg
        End If
    End If

    If Cells(i, 2) = 995 Then
        If Not Cells(i, 3) <= 17999 And Cells(i, 3) >= 17000 Then
            GoTo errormsg
        End If
    End If
Next i
Exit Sub
errormsg:
MsgBox "You have problems", vbOKOnly

End Sub

Is there a way to make sure includes the "various" cells? Those do not need to have the check run on them. Those cells are fine.

[nigelbloomy]

Does this work for that?

Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To LastRow
    If Cells(i, 2) = "0890-fed" Or Cells(i, 2) = "0890-DBW" Then
        If Not (Cells(i, 3) <= 10199 And Cells(i, 3) >= 10100) Or Cells(i, 3) = "Various" Then
            GoTo errormsg
        End If
    End If

    If Cells(i, 2) = 995 Then
        If Not (Cells(i, 3) <= 17999 And Cells(i, 3) >= 17000) Or Cells(i, 3) = "Various" Then
            GoTo errormsg
        End If
    End If
Next i
Exit Sub
errormsg:
MsgBox "You have problems", vbOKOnly

End Sub

[kevingcp]

It's not skipping over the various cells, I'm trying to skip over those but I can't seem to get the code right.

[jaslake]

Hi kevingcp

If I understand the issue, use AutoFilter (filter out "various") then do the test on SpecialCells(xlcelltypevisible).

[nigelbloomy]

Sorry. It gets confusing when trying to use too many if and or statements in one line. I guess I got the format wrong. This way works and is more understandable.

Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To LastRow
    If Cells(i, 3) <> "Various" Then
    If Cells(i, 2) = "0890-fed" Or Cells(i, 2) = "0890-DBW" Then
        If Not (Cells(i, 3) <= 10199 And Cells(i, 3) >= 10100) Then
            GoTo errormsg
        End If
    End If

    If Cells(i, 2) = 995 Then
        If Not (Cells(i, 3) <= 17999 And Cells(i, 3) >= 17000) Then
            GoTo errormsg
        End If
    End If
    End If
Next i
Exit Sub
errormsg:
MsgBox "You have problems", vbOKOnly

End Sub