Excel Mac VBA Loop Through Cells and reset some values

Question

I currently have 12 copies of a Workbook - one for each one of our company's locations. There are multiple columns, and one row for each day of the month. Every day the Location Manager fills out 2 columns, and the rest are calculated automatically. There are a few "complicated" users out there who refuse to enter a zero in the second column if there is no amount for that day, and they leave it blank, causing errors across the worksheet.

There will always be a value for each day in column A, so I would like to run a macro when the users click save that will check column A for a value. If there is a value then that means this day has passed, and there SHOULD be a value in column D (even if it's zero).

To sum this up, if column A is not blank, check column D. If it is blank, fill it in with a zero. If it is not blank, the user entered a value, so we can then move on. When it gets to a cell in column A that is blank, just quit the entire process.

I've been browsing the Internet for a few days trying to figure this out, and here is what I have so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim curCell As Range
  'Labor Flow Sheet'.Select

  For Each curCell in Range(A1:D31)
    If curCell.Value = "" Then
     <???????>
    End If
  Next curCell
End Sub

I've set the range to the entire area, however I'm not sure how to know that the loop is only going to check column A. Should the range be (A1:A31) instead? Then how do I tell it to skip over in the same row to column D to check that value?

I truly appreciate anyone's help in advance. I hope to be of some help in this community answering other questions wherever I can as well!

Thanks again

Answer 1

I didn't get a chance to test this but it should work all you will have to do is add your own workbook & sheet name.

Sub test()
Dim Ws As Worksheet
Dim X As Long

Application.ScreenUpdating = False
Set Ws = Workbooks("").Sheets("Sheet1") ' add your workbook here

For X = 1 To Ws.Range("A" & Rows.Count).End(xlUp).Row
If Not Ws.Cells(X, 1).Value = vbNullString Then
    If Ws.Cells(X, 4).Value = vbNullString Then      
    Ws.Cells(X, 4).Value = "0"        
    End If        
ElseIf Ws.Cells(X, 1) = vbNullString Then        
    Exit Sub    
End If
Next X

Application.ScreenUpdating = True
End Sub

Depending on the size of your workbook i think it is worth disabling screen updating but its not a necessity also using VbNullString is better than "" as this take up no memory.

Hope it helps,

Tom