Help with For Function

[Raza1]

Hi Guys,

I have the below macro to check the cell values and if empty assign a text value and give the user a message to enter the required info. After this I want to make sure if they have entered the required cells but not really sure how to do that. Any help is highly appreciated.

Thank you

Private Sub CommandButton1_Click()


For Each cell In Range("E9:E22")
If cell.Value <> "" Then

If (cell.Offset(0, -1).Value = "") And (cell.Offset(0, -2).Value = "") Then
cell.Offset(0, -1).Value = "Please enter Account."
cell.Offset(0, -2).Value = "Please enter Dept/Restaurant."

ElseIf (cell.Offset(0, -1).Value = "") Then
cell.Offset(0, -1).Value = "Please enter Account."

ElseIf (cell.Offset(0, -2).Value = "") Then
cell.Offset(0, -2).Value = "Please enter Dept/Restaurant."

MsgBox "Please check & enter missing data"


End If
End If


Next


End Sub

 

[jproffer]

This will force the user to enter your required data before they can go any further. No checking required, they just can't do anything else until they enter something in those ranges.

Sub test2()
Dim cell As Range
Dim Ans As String



For Each cell In Range("E9:E22")
    If cell.Value <> "" Then

        If (cell.Offset(0, -1).Value = "") Then
            Do Until Ans <> ""
                Ans = InputBox("Please enter Account for row # " & cell.Row)
            Loop
            
            cell.Offset(0, -1).Value = Ans
            
        End If
        
        Ans = ""
        
        If (cell.Offset(0, -2).Value = "") Then
            Do Until Ans <> ""
                Ans = InputBox("Please enter Dept. for row # " & cell.Row)
            Loop
            
            cell.Offset(0, -2).Value = Ans
            
        End If
        
    End If

    Ans = ""

Next

    
End Sub

Does that work for you, or would you rather do something more similar to what you had originally?

EDIT: I took it out of the command_button event for testing and forgot to put it back so you'll have to just copy everything except the first and last lines, and just paste it between your original first/last lines.

 

[=ODIN=]

Ooops... looks like jproffer already solved this, but since I already wrote the code, please see below. The logic here, is that you would keep hitting the command button, and once everything was filled in, you would get the msgbox "All Data Filled In Correctly"

Private [COLOR=#333333]Sub CommandButton1_Click()[/COLOR]


Dim dataGood As Boolean
dataGood = True


For Each cell In Range("E9:E22")
    If cell.Value <> "" Then
     
        If cell.Offset(0, -1).Value = "" Or cell.Offset(0, -1).Value = "Please enter Account." Then
            cell.Offset(0, -1).Value = "Please enter Account."
            dataGood = False
        End If
        
        If cell.Offset(0, -2).Value = "" Or cell.Offset(0, -2).Value = "Please enter Dept/Restaurant." Then
            cell.Offset(0, -2).Value = "Please enter Dept/Restaurant."
            dataGood = False
        End If
    
    End If
Next cell


If dataGood = True Then
    MsgBox "All Data Has Been Filled In Correctly", vbOKOnly
Else


    MsgBox "Please check & enter missing data", vbOKOnly
End If




End Sub

 

[Raza1]

Thank you for your help. I will try this out and let you know.

 

[Raza1]

Thank you both. Both the solutions were perfect.