If No data input, then stop and msgbox

[sassriverrat]

Hello-

continuing tweaking a module. The module takes data from a bunch of cells and runs a series of formulas (via a button) and the answer comes up in a msgbox. Obviously if a critical cell doesn't have data, it'll stop everything.

I started some of the coding for one of the modules (there are 15 cells in some of the heavier formulas). Basically, I'd like for the macro to go pull data from the cell. However, if the cell is blank, I'd like the macro to open a msgbox that says "No Data Input, please input data" and then it's either got a retry button that takes you to the empty cell or a cancel button that stops the whole macro so the user can do something else.

Here is one of the shorter ones (figured someone can look at it quickly this way). this particular one is an Estimated time of arrival (ETA) calculator.

Thanks-

Sub ETA_CALC1()

Dim Path1 As Double
Dim Path2 As Date
Dim Path3 As Double
Dim Path4 As Double
Dim Path5 As Double
Dim path6 As Double
Dim Path7 As Date
Dim Path8 As Date
Dim DTG As Double
Dim resp As Integer
'This says that the Distance to go should be in D10 but if we want to use a different mileage than what today's report had list, _
(D10 is on the list), we can input into S32 to use our own distance
'This piece ensures "ETA Arrival ZD" is ready
Worksheets("Developer").Range("F3").FormulaR1C1 = "=IF(AND((Notes!R[10]C[6]+Notes!R[10]C[7])<((R[1]C[-3]+R[1]C[-2])),(Notes!R[11]C[6]+Notes!R[11]C[7])>((R[1]C[-3]+R[1]C[-2]))),""Yes"",""No"")"






If ActiveSheet.Range("S29").Value = "" Then
    DTG = ActiveSheet.Range("D10").Value
    Else: DTG = ActiveSheet.Range("S29").Value
End If


'this is where I would do each of the "error checking" basically- just like the next line
'If ActiveSheet.Range("R28").Value = "" Then
     msgbox(No Data Input, Please Try Again.,vbretrycancel)
     Else: (ActiveSheet.Range("R28").Value)
End If
'Do this for each Path.....
Path1 = MilitarytoTime(ActiveSheet.Range("R28").Value)



Path2 = ActiveSheet.Range("T28").Value
Path3 = DTG


Path5 = Sheets("Developer").Range("G2").Value
path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (TimeSerial(Path5, 0, 0))) - (Path7 + Path8 + (TimeSerial(path6, 0, 0)))) * 24))




resp = MsgBox("Based on your desired Arrival Time/Date and your mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " knots" & vbCrLf & vbCrLf & "Would you like to use this ETA for Today's Report?", vbYesNo)
If resp = vbYes Then
    ActiveSheet.Range("W33").Value = Format(Path1, "hh:mm;@")
    ActiveSheet.Range("Y33:Z33").Value = Path2
    End If
    ActiveSheet.Range("R29").Select
End Sub

 

[Logit]

.
This macro will check the cells named in the array and determine if their is data. If no data, a msgbox prompt displays to advise such and lists
the cell identification.

Perhaps you can work this into your project.

Option Explicit


Sub Sample()
    Dim rng As Range, aCell As Range
    Dim MyAr() As Variant
    Dim n As Long, i As Long


    '~~> Change this to the relevant sheet
    With Sheet1
        '~~> Non Contiguous range
        Set rng = .Range("A1,C1,B3,D3,C5,G5")


        '~~> Get the count of cells in that range
        n = rng.Cells.Count


        '~~> Resize the array to hold the data
        ReDim MyAr(1 To n)


        n = 1


        '~~> Store the values from that range into
        '~~> the array
        For Each aCell In rng.Cells
            If aCell.Value = "" Then
                MsgBox "Cell " & aCell.Address & "is blank.", vbExclamation, "Need Data !"
            End If
            n = n + 1
        Next aCell
    End With


   
End Sub