Error Message when running code, Runtime Error 9, Subscript Out Of Range

[Moseth]

Hi, Can someone tell me why im getting this issue

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If (Sheets("Supplier Checklist").Range("D24").Value) = "No" And Sheets("Supplier Checklist").Range("E21") = "" Then
    SaveAsUI = False
    MsgBox "Please define the non compliance/PCA where a No is entered", vbExclamation
    Sheets("Sheet1").Range("D13").Select
End If
End Sub

 

[RoryA]

One of your sheet names must be wrong.

 

[Moseth]

Thankyou,

If I want to extend the range from D21 to D24 -"No" and Range E21, E23, E24= ""


If (Sheets("Supplier Checklist").Range("D21").Value) = "No" And Sheets("Supplier Checklist").Range("E21") = "" Then
SaveAsUI = False
MsgBox "Please define the non compliance/PCA where a No is entered", vbExclamation
Sheets("Supplier Checklist").Range("E21").Select
End If

How do I do this?

 

[RoryA]

Meaning what - all of D21:D24 are No, or any of them? Same for column E.

 

[Moseth]

so if D21 is no and any one of E21, G21,H21,I21 is Blank then cancel save
so if D22 is no and any one of E22, G22,H22,I22 is Blank then cancel save
going all the way to D129 in this format, missing a few rows in the process

 

[RoryA]

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rw as long
with Sheets("Supplier Checklist")
for rw = 21 to 129
If .Cells(rw, "D").Value = "No" And (Len(.cells(rw, "E").Value) = 0 Or Len(.cells(rw, "G").Value) = 0 Or Len(.cells(rw, "H").Value) = 0 Or Len(.cells(rw, "I").Value) = 0) Then
    SaveAsUI = False
    MsgBox "Please define the non compliance/PCA where a No is entered", vbExclamation
    .Cells(rw, "D").Select
    Exit For
End If
Next
End With
End Sub

 

[Moseth]

Thankyou!,
Finally within 21 to 129, I dont want the code being applied to rows 25,32,36,39,46,52,60,69,74,79,86,92,94,96,99,103,110,115 , Appreciate your help

 

[RoryA]

Oh, it's one of those expanding questions...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rw as long
with Sheets("Supplier Checklist")
for rw = 21 to 129
Select case rw
case 25,32,36,39,46,52,60,69,74,79,86,92,94,96,99,103,110,115
' do nothing
case else
If .Cells(rw, "D").Value = "No" And (Len(.cells(rw, "E").Value) = 0 Or Len(.cells(rw, "G").Value) = 0 Or Len(.cells(rw, "H").Value) = 0 Or Len(.cells(rw, "I").Value) = 0) Then
    SaveAsUI = False
    MsgBox "Please define the non compliance/PCA where a No is entered", vbExclamation
    .Cells(rw, "D").Select
    Exit For
End If
end select
Next
End With
End Sub

 

[Moseth]

Thankyou!