Verifying Certain Cells have input

[gheyman]

I have code that I want to run only if several cells have data in it.

E7
E9
E11
E13
E15
M7
M9
M11
M13
M15

I don't want it to do an If for each individual cell (If, then, If, Then....)
I want to run the code and check these. If any are blank, stop the code and open UserForm5

Private Sub CommandButton1_Click()
    
    Calculate

    Dim CurRow As Integer
    Dim NumMonths As Integer
    Dim Amount As Double
    Dim Dcurv As Integer
         
    'Changed
    Dcurv = Cells(71, 2).Value
    NumMonths = Cells(75, 2).Value
    Amount = Cells(72, 2).Value
    
    Range(Cells(77, 1), Cells(570, 2)).ClearContents
    
    CurRow = 76

    a = 1 / NumMonths
    W = Amount
    Min = 1
    'All other curves-------------
    BOG = 0.01 * Dcurv
    S = BOG
    For i = 1 To 40
       S = Sqr(BOG / (3 - (2 * S)))
    Next
    p = 0
    CL = 0
    For K = 1 To NumMonths
       Cells(K + CurRow, 1).Value = K
       p = p + a
       X = (1 - 2 * S) * p * (((-4 * p + 8) * p - 3) * p) + 2 * S * p
       C = X * X * (3 - 2 * X)
       XX = Min
       Cells(K + CurRow, 2).Value = ((C - CL) * XX * W)
       CL = C
       
       If K = 1 Then
        Cells(K + CurRow, 1).Formula = "=EOMONTH(B73,0)"
        Cells(K + CurRow, 1).NumberFormat = "mmm-yy"
       Else
        Cells(K + CurRow, 1).FormulaR1C1 = "=EOMONTH(R[-1]C,1)"
        Cells(K + CurRow, 11).NumberFormat = "mmm-yy"
       End If
     Next
     
     Range("S1").Activate
     Calculate
End Sub

Thanks

 

[Joe4]

I have code that I want to run only if several cells have data in it.

E7
E9
E11
E13
E15
M7
M9
M11
M13
M15

I don't want it to do an If for each individual cell (If, then, If, Then....)
I want to run the code and check these. If any are blank, stop the code and open UserForm5

Here is a short block of code to show you how to do that. I will leave it to you to incorporate it into your code:

    Dim r As Long
    
    For r = 7 To 15 Step 2
        If (Cells(r, "E") = "") Or (Cells(r, "M") = "") Then
            'code to open User Form here
            Exit For
        End If
    Next r

 

[gheyman]

Thanks, Joe. But once I exit out of the UserForm2 it continues/finishes through the code. I need it to exit the code.

Private Sub CommandButton1_Click()
    
    ActiveSheet.Unprotect "54TPL0102"
    Calculate
    
    Dim r As Long
    
    For r = 7 To 13 Step 2
        If (Cells(r, "E") = "") Or (Cells(r, "M") = "") Then
            UserForm2.Show
            Exit For
        End If
    Next r

    Dim CurRow As Integer
    Dim NumMonths As Integer
    Dim Amount As Double
    Dim Dcurv As Integer
         
    'Changed
    Dcurv = Cells(71, 2).Value
    NumMonths = Cells(75, 2).Value
    Amount = Cells(72, 2).Value
    
    Range(Cells(77, 1), Cells(570, 2)).ClearContents
    
    CurRow = 76

    a = 1 / NumMonths
    W = Amount
    Min = 1
    'All other curves-------------
    BOG = 0.01 * Dcurv
    S = BOG
    For i = 1 To 40
       S = Sqr(BOG / (3 - (2 * S)))
    Next
    p = 0
    CL = 0
    For K = 1 To NumMonths
       Cells(K + CurRow, 1).Value = K
       p = p + a
       X = (1 - 2 * S) * p * (((-4 * p + 8) * p - 3) * p) + 2 * S * p
       C = X * X * (3 - 2 * X)
       XX = Min
       Cells(K + CurRow, 2).Value = ((C - CL) * XX * W)
       CL = C
       
       If K = 1 Then
        Cells(K + CurRow, 1).Formula = "=EOMONTH(B73,0)"
        Cells(K + CurRow, 1).NumberFormat = "mmm-yy"
       Else
        Cells(K + CurRow, 1).FormulaR1C1 = "=EOMONTH(R[-1]C,1)"
        Cells(K + CurRow, 11).NumberFormat = "mmm-yy"
       End If
     Next
     
    'Get Monthly Values Paste them
    Range("S3:S62").FormulaR1C1 = "=IFERROR((INDEX(C[-17],MATCH(RC18,C[-18],0))),"""")"
    Range("S3:S62").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
     
     Range("M13").Activate
     Calculate
     ActiveSheet.Protect "54TPL0102"
     
End Sub

 

[Joe4]

Then change the:

Exit For

line to:

Exit Sub

 

[Rick Rothstein]

I have code that I want to run only if several cells have data in it.

E7
E9
E11
E13
E15
M7
M9
M11
M13
M15

I don't want it to do an If for each individual cell (If, then, If, Then....)
I want to run the code and check these. If any are blank, stop the code and open UserForm5

This (long) single line of code will exit the Sub if any one or more of the above identified cells is empty...

[table="width: 500"]
[tr]
	[td]If InStr("," & Join(Application.Transpose(Application.Index(Cells, [{7;9;11;13;15}], 5)), ",") & "," & Join(Application.Transpose(Application.Index(Cells, [{7;9;11;13;15}], 13)), ",") & ",", ",,") Then Exit Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

Another possible approach to consider.

Dim rBlanks As Range

On Error Resume Next
Set rBlanks = Range("E7,E9,E11,E13,E15,M7,M9,M11,M13,M15").SpecialCells(xlBlanks)
On Error GoTo 0
If rBlanks Is Nothing Then
  'All complete so do your normal stuff here
Else
  'Do your stuff with UserForm5 here
End If