Check if cell is empty?

[option]

Hey guys,

Quick question: I need to have excel check this workbook for user errors. We have employees populate the spreadsheets in the workbook to keep track of their daily tasks. The only problem is, say Joe Shmo puts down the time he works on a task, but forgets to put the volume. Since we are paid by our activity, Joe wouldn't be paid correct if we have no volume associated with time. Basically, I need excel to check if one cell is populated based on another. Like "If cell E6 = > 0, then check cell P6. If P6= populated, continue. if empty, go to err_hndle." And vice versa (if p6 = populated and e6 isnot, go to err_hndle). Confused yet? haha Thanks for all of the help guys, you've been amazing so far!

 

[pl456]

I am a little confused but would something like this do?

=IF(B2="","no data","data entered")

 

[option]

Well what I am trying to do is have the user click a button before submitting the sheet to check for errors. Basically, for each task there is usually 3 sections that need to be completed on the form. So, if the user fills out sections 1 and 2 but leaves 3 blank, i need that button to recognize that. I'm doing it all in VBA, here is what I've got so far:

Public Function ValidateForm() As Boolean
On Error GoTo errHndl
Dim ws_TASKS As Worksheet, ws_RE As Worksheet, ws_MAIN As Worksheet, ws_ProRE As Worksheet
Dim t As Integer, v As Integer
Dim dbl_VOL As Double, dbl_HOURS As Double, str_Task As String
Dim dbl_EMPID As Double, dte_DATE As Date
Dim str_cell(1 To 2) As String, dte_START As Date, dte_STOP As Date
Dim st_TIME As String


ValidateForm = True

Set ws_TASKS = VBAProject.ThisWorkbook.Sheets("TASKS")
Set ws_RE = VBAProject.ThisWorkbook.Sheets("LC RE")
Set ws_MAIN = VBAProject.ThisWorkbook.Sheets("Main")
Set ws_ProRE = VBAProject.ThisWorkbook.Sheets("Pro RE")
t = 2 'Row indicator for Tasks tab
st_TIME = ws_MAIN.Range("C12")
'Check for Employee ID/Date
If IsEmpty(ws_MAIN.Range("C4")) = True Then
Call AddError("Main", "Employee ID", "Please enter employee id")
ValidateForm = False
End If
'GoTo extHndl 'Employee ID
If IsEmpty(ws_MAIN.Range("C6")) = True Then GoTo extHndl 'Date
If IsEmpty(ws_MAIN.Range("C12")) = True Then GoTo extHndl 'Shift Start
If IsEmpty(ws_MAIN.Range("C14")) = True Then GoTo extHndl 'Shift End

If ws_RE.Range("R8") < 0 Then 'Manual Other 1
If IsEmpty(ws_RE.Range("AL8")) = True Then GoTo extHndl
End If
If ws_RE.Range("P23") > 0 Then
If IsEmpty(ws_RE.Range("AL8")) = True Then GoTo extHndl
End If
If ws_RE.Range("P24") > 0 Then
If IsEmpty(ws_RE.Range("E24")) = True Then GoTo extHndl
End If
If ws_RE.Range("P25") > 0 Then
If IsEmpty(ws_RE.Range("E24")) = True Then GoTo extHndl
End If

'Start going through Tasks
Do Until IsEmpty(ws_TASKS.Range("A" & t)) = True 'Loop through Tasks until end
If IsEmpty(ws_TASKS.Range("C" & t)) = False _
Or IsEmpty(ws_TASKS.Range("E" & t)) = False Then 'If Volume/Hours cells are not empty
'Grab Volume
str_cell(1) = ws_TASKS.Range("C" & t) 'Grab Cell
If str_cell(1) <> "" Then
If IsEmpty(ws_RE.Range(str_cell(1))) Then
ws_RE.Range(str_cell(1)) = 0
dbl_VOL = 0
Else
dbl_VOL = ws_RE.Range(str_cell(1))
End If
End If

'Grab Hours
str_cell(1) = ws_TASKS.Range("E" & t) 'Grab Cell
If str_cell(1) <> "" Then
If IsEmpty(ws_RE.Range(str_cell(1))) Then
ws_RE.Range(str_cell(1)) = 0
End If
End If

'Grab Start and Stop Time
str_cell(1) = ws_TASKS.Range("G" & t) 'Start
str_cell(2) = ws_TASKS.Range("H" & t) 'Stop
If str_cell(1) <> "" Then
If IsEmpty(ws_RE.Range(str_cell(1))) = True Then
If IsEmpty(ws_RE.Range(str_cell(1))) = True Then
GoTo extHndl
End If
'elseif
End If
End If


'Grab Task
str_cell(1) = ws_TASKS.Range("I" & t)
If str_cell(1) <> "" Then
If IsEmpty(ws_RE.Range(str_cell(1))) = True And dbl_VOL > 0 Then
GoTo extHndl
End If
End If
End If
t = t + 1
Loop

'ValidateForm = True
extHndl:
Exit Function
errHndl:
MsgBox "There has been an error: " & Err.Description, vbCritical, "ERROR"
Resume extHndl
End Function

 

[option]

98 views, 1 response.....

 

[chergh]

Well you've not exactly defined your problem. You say you need to validate your spreadsheet and you've written a function to do that. Your title asks 'check if cell is empty' and your using the isempty to do exactly that.

As far as I can see there isn't actually a question to answer here so don't act offended about 98 views and 1 reply when you haven't defined your question clearly.

 

[Brianwarnock]

I agree with Chergh, further you have posted a fair bit of code but not indicated where, How and underwhat circumstances it is not working eg what data. Note that isempty checks for non initialised or emptied cells, a blank would return false and as you are checking user input somebody might "empty" a cell using the space bar.

Brian

 

[Brianwarnock]

This scenario

Like "If cell E6 = > 0, then check cell P6. If P6= populated, continue.
if empty, go to err_hndle."
And vice versa
(if p6 = populated and e6 isnot, go to err_hndle).

would be
.
.
If e6=>0 And isempty(P6) goto error handler
If p6=>0 And isempty(e6) goto error handler
continue

in other words check for the errors and exit the code sequence not for correct entries and try to continue.

Brian

 

[option]

Hey guys,

Quick question: I need to have excel check this workbook for user errors. We have employees populate the spreadsheets in the workbook to keep track of their daily tasks. The only problem is, say Joe Shmo puts down the time he works on a task, but forgets to put the volume. Since we are paid by our activity, Joe wouldn't be paid correct if we have no volume associated with time. Basically, I need excel to check if one cell is populated based on another. Like "If cell E6 = > 0, then check cell P6. If P6= populated, continue. if empty, go to err_hndle." And vice versa (if p6 = populated and e6 isnot, go to err_hndle). Confused yet? haha Thanks for all of the help guys, you've been amazing so far!

There is my dilemma, chergh. As far as the code I posted, did you read my comment right above the coding?

"Well what I am trying to do is have the user click a button before submitting the sheet to check for errors. Basically, for each task there is usually 3 sections that need to be completed on the form. So, if the user fills out sections 1 and 2 but leaves 3 blank, i need that button to recognize that. I'm doing it all in VBA, here is what I've got so far:"

If I could have made it any clearer, I would have.
Brianwarnock - Thanks for your advice as far as the direction I should be looking into. That's all I was looking for.

Thanks for reading the post thoroughly enough to understand.