Verifying Certain Cells have input

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,460
Office Version
  1. 365
Platform
  1. Windows
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:
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
Thanks, Joe. But once I exit out of the UserForm2 it continues/finishes through the code. I need it to exit the code.

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,460
Office Version
  1. 365
Platform
  1. Windows
Then change the:
Code:
Exit For
line to:
Code:
Exit Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
[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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,253
Office Version
  1. 365
Platform
  1. Windows
Another possible approach to consider.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top