PDA

View Full Version : Solved: Not sure how to fix this loop? Help Please



doctortt
03-28-2011, 11:08 PM
Can someone help? I want to check 5 cells whether they're blank or not.
They're (4,3) (6,3) (8,3) and (4,7) (6,7)

The probelm is after row 6, there is only one cell left to check, which is (8,3). However, the codes below will check (8,7) and definately throw out an error message. Is there a way to fix the codes and make Excel to bypass (8,7)? I'm new to this so my codes might not be the perfect way to do what I want.

Row = 4
For I = 1 To 3
If (IsEmpty(Cells(Row, 3))) Or (IsEmpty(Cells(Row, 7))) Then
MsgBox ("No Blank")
Exit Sub
Else
Row = Row + 2
End If
Next I

GTO
03-29-2011, 12:12 AM
Maybe:


Sub Exa1()
Dim Row As Long, i As Long

For Row = 4 To 8 Step 2
If Not Row = 8 And (IsEmpty(Cells(Row, 3)) Or IsEmpty(Cells(Row, 7))) Then
MsgBox "no blank"
Exit Sub
ElseIf Row = 8 And IsEmpty(Cells(Row, 3)) Then
MsgBox "no blank"
Exit Sub
End If
Next
End Sub

p45cal
03-29-2011, 02:19 AM
alternativelySub blah()
For Each cll In Range("C4,C6,C8,G4,G6").Cells
If IsEmpty(cll) Then
MsgBox ("No Blank")
Exit Sub
End If
Next cll
'MsgBox "ok"
End Sub
or a bit shorter:For Each cll In Range("C4,C6,C8,G4,G6").Cells
If IsEmpty(cll) Then MsgBox ("No Blank"): Exit Sub
Next cll

GTO
03-29-2011, 03:04 AM
alternatively...and nicer:)

doctortt
03-29-2011, 02:28 PM
Thanks a lot p45. It works like a charm.

By the way, if the user instead hits the spacebar to delete the cell, this script won't be able to throw out the error because of the empty space in the blank cell. What can I do to make the VBA codes to detect this?Thanks again

p45cal
03-29-2011, 04:20 PM
For Each cll In Range("C4,C6,C8,G4,G6").Cells
If Trim(cll.Value) = "" Then MsgBox ("No Blank"): Exit Sub
Next cll

doctortt
04-01-2011, 10:43 PM
thanks big guy