CODEWITHSUNDEEP
excelvba
mSU
mSU

Reputation: 25

Dynamic Loop Range, VBA

I'm creating macro Excel that will check if there is empty cells in specific range before closing the Workbook. I have an issue that the range is dynamic , and I have cell that contain the range that I want to check each time, how can I use cell value inside VBA as range.

This is the cell that I want to read range from it

and this is the VBA

Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each cell In Range("A2:E2") If cell.Value = "" Then MsgBox "Please, fill empty cells", vbInformation, "Warning" cell.Select Cancel = True Exit Sub End If Next End Sub

Private Sub Workbook_Open()

End Sub

vba code

thank you

Upvotes: 1

Views: 453

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9568

Try this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim e, cell As Range
    e = Range("O11").Value
    If IsNumeric(Evaluate("SUM(" & e & ")")) And Not IsNumeric(e) Then
        For Each cell In Range(e)
            If cell.Value = "" Then
                MsgBox "Please, Fill Empty Cells", vbInformation, "Warning"
                cell.Select
                Cancel = True: Exit Sub
            End If
        Next cell
    End If
End Sub

Upvotes: 1

Related Questions