2
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
Thanks so much Tom! Thats the direction I needed! Can you tell me how to have it not start until row 5? I have some header information in rows 1-4 that I want to bypass. Thanks so much again! – Dru Darby – 2013-10-17T16:40:38.113
no problem, if you change X = 1 to X = 5 on line 10 this will start the loop at row 5. – Tom D – 2013-10-18T06:36:55.333