CODEWITHSUNDEEP
vbaexcelif-statement
James
James

Reputation: 499

Working with Multiple Ranges in a Worksheet

I have a worksheet named Start Page which i am working with. The objective of my code is to inform the user if a cell is blank , that a value needs to be entered through a message box. I have been able to do it with the use of an If statement. See my code below:

Dim rng As Range
Set rng = Sheets("Start page").Range("A4")
If rng = "" Then
    MsgBox "Please enter Date from A4", vbOKCancel
    Exit Sub
End If

However i trying to avoid using multiple If statements. The cells i am referring to and message boxes are below:

Sheets(“Start page”).range(“A4”) msgbox “Please enter First valuation Date inA4”

Sheets(“Start page”).range(“F5”) msgbox “ Please enter the BPS”

Sheets(“Start page”).range(“F7”) msgbox “ Please enter Account number”

Sheets(“Start page”).range(“F9”) msgbox “ Please enter Date”

Sheets(“Start page”).range(“F11”)msgbox “ Please enter Number of Periods”

Sheets(“Start page”).range(“F13”)msgbox “Please Enter number of Days in the Year"

Is there an efficient way of doing this ?

Upvotes: 0

Views: 91

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

This is how I am picturing your sheet.

Set up

The code will loop through the ranges and the first one it finds empty will pop up a message.

Pop Up Message

The code uses the cell above in the message.

Cell Above

Sub FndBlnk()
    Dim Rng As Range, c As Range

    Set Rng = Range("A4,F5,F7,F9,F11,F13")

    For Each c In Rng.Cells

        If c <> "" Then
        Else: MsgBox "Enter " & c.Offset(-1)
        c.Select
        Exit Sub
        End If
    Next c

End Sub

Upvotes: 1

Related Questions