Excel VBA Looping

Asked at 2017-01-11 20:02:52Z
  • 5 Subscribers
  • 114 Views
0

Okay bear with me on this, I am pretty new to VBA in Excel. I have been trying to figure out how to loop in a time sheet I made for error checking for a few days now and I am just dumbfounded.

My time sheet looks like this

So I have been trying to get a loop to look for empty cells.

My range would be ("C4:I4") & I need to run this on 29 rows starting with C4.

  • If a row has data in each cell in the range then I would like it to GoTo 500.
  • If a row has data in any cells but all are not filled in I need it to display a MsgBox to tell the user what needs to be filled in.
  • If an entire range in a row besides the first is empty then I need to GoTo 500 to run the save file part of the code.

Any help would be greatly appreciated. I have scoured through the forums and tried to get multiple looping examples to work for me but have been unable to do so.

Sub checkemptycells()

    Dim x As String

    Range("C1").Select
    If ActiveCell.Text = "" Then
        MsgBox "Pick Your Name"
        GoTo 1000
    End If

    Range("G1").Select
    If ActiveCell.Text = "" Then
        MsgBox "ENTER A DATE"
        GoTo 1000
    End If

    Range("C4").Select
    If ActiveCell.Text = "" Then
        MsgBox "Need Job#"
        GoTo 1000
    End If

    Range("D4").Select
    If ActiveCell.Text = "" Then
        MsgBox "need qty."
        GoTo 1000
    End If

    Range("E4").Select
    If ActiveCell.Text = "" Then
        MsgBox "Need Work Order!"
        GoTo 1000
    End If

    Range("F4").Select
    If ActiveCell.Text = "" Then
        MsgBox "Pick a Dept.!"
        GoTo 1000
    End If

    Range("G4:I4").Select
    If ActiveCell.Text = "" Then
        MsgBox "Fill Start & Stop times on this line"
        GoTo 1000
    End If

    Dim FilePath As String
    Dim FileName As String
    Dim MyDate As String
    Dim USER As String

    FilePath = "Z:\TIMESHEETS\"
    MyDate = Format(Now(), "mm_dd_yyyy_")
    USER = (ActiveSheet.Range("  C1"))

    FileName = FilePath & MyDate & USER
    ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlWorkbookNormal
    ActiveWorkbook.Close
    OpenAfterPublish = False
    xls.DisplayAlerts = False

1000
End Sub

1 answers in total

0
Cyril Posted at 2017-01-11 20:40:01Z

Would something like this work?

Dim i as Variant
Dim j as Variant

For i = 4 to 32
For j = 3 to 9

    If Cells(i,j).Value="" Then

    MsgBox "Missing value in Cell(" & i & " ," & j & ")."

    End If

Next j
Next i

Answer this questsion