excel 97: if then and for next loops help

[JMCN]

hi,

i'm really lost with the if... then, for...next loops. what i want to
do is to nest the for..next loop statement in an if.. then. the user
would click the "finish" button which the for... next loop checks to
see if there are amounts in columns c & g with dates in columns B & F.
if there are no dates in column B & F then a message should appear
"please enter tx date". If the user did enter both the amounts with
dates, then a different message should appear with "finished and
save."

here is an example but i cannot get it to work do to missing objects
and i have no idea what the structure should be. i have it half way
finished but any any advice would be appreciated
thanks jung
+++++++++++
Private Sub Finish_Click()
'check to see if they entered a date "mm/dd/yyyy" and if yes have
"finish" message,
otherwise message will say "please enter tx date"

'if loop1 = true then
For LOOP1 = 27 To 46
If (Range("C" & LOOP1).Value <> 0) Then
If (IsDate(Range("B" & LOOP1).Value = False)) Or Range("B" &
LOOP1).Value = "" Then
X = ("Missing TX Date")
Range("B" & LOOP1).Value = X
End If
End If
Next LOOP1
For LOOP2 = 52 To 70
If (Range("C" & LOOP2).Value <> 0) Then
If (IsDate(Range("B" & LOOP2).Value = False)) Or Range("B" &
LOOP2).Value = "" Then
X = ("Missing TX Date")
Range("B" & LOOP2).Value = X
End If
End If
Next LOOP2
For LOOP3 = 27 To 46
If (Range("G" & LOOP3).Value <> 0) Then
If (IsDate(Range("F" & LOOP3).Value = False)) Or Range("F" &
LOOP3).Value = "" Then
X = ("Missing TX Date")
Range("F" & LOOP3).Value = X
End If
End If
Next LOOP3
For LOOP4 = 52 To 70
If (Range("G" & LOOP4).Value <> 0) Then
If (IsDate(Range("F" & LOOP4).Value = False)) Or Range("F" &
LOOP4).Value = "" Then
X = ("Missing TX Date")
Range("F" & LOOP4).Value = X
End If
End If
Next LOOP4

'msgbox "plesase make sure there is a tx date."
'elseif
'msgbox "finished!! please save!!"

end if
End Sub

 

[Tom Ogilvy]

If x <> "" then a missing date was found:

Private Sub Finish_Click()
'check to see if they entered a date "mm/dd/yyyy" and if yes have
'"finish" message,
'otherwise message will say "please enter tx date"
x = ""
'if loop1 = true then
For LOOP1 = 27 To 46
If Range("C" & LOOP1).Value <> 0 Then
If IsDate(Range("B" & LOOP1).Value) = False _
Or Range("B" & LOOP1).Value = "" Then
x = ("Missing TX Date")
Range("B" & LOOP1).Value = x
End If
End If
Next LOOP1
For LOOP2 = 52 To 70
If Range("C" & LOOP2).Value <> 0 Then
If IsDate(Range("B" & LOOP2).Value) = False _
Or Range("B" & LOOP2).Value = "" Then
x = ("Missing TX Date")
Range("B" & LOOP2).Value = x
End If
End If
Next LOOP2
For LOOP3 = 27 To 46
If Range("G" & LOOP3).Value <> 0 Then
If IsDate(Range("F" & LOOP3).Value) = False _
Or Range("F" & LOOP3).Value = "" Then
x = ("Missing TX Date")
Range("F" & LOOP3).Value = x
End If
End If
Next LOOP3
For LOOP4 = 52 To 70
If Range("G" & LOOP4).Value <> 0 Then
If IsDate(Range("F" & LOOP4).Value) = False _
Or Range("F" & LOOP4).Value = "" Then
x = ("Missing TX Date")
Range("F" & LOOP4).Value = x
End If
End If
Next LOOP4
If x <> "" Then
MsgBox "plesase make sure there is a tx date."
Else
'msgbox "finished!! please save!!"
End If

End Sub

A more compact way of doing this is:

Sub Finish_Click()
Dim bFail As Boolean
Dim cell As Range

For Each cell In Range("C27:C46,C52:C70")
If cell.Value <> 0 Then
If Not IsDate(cell.Offset(0, -1)) Then
cell.Offset(0, -1).Value = "Missing tx Date"
bFail = True
End If
End If
If cell.Offset(0, 4).Value <> 0 Then
If Not IsDate(cell.Offset(0, 3)) Then
cell.Offset(0, 3).Value = "Missing tx Date"
bFail = True
End If
End If
Next
If bFail Then
MsgBox "Dates are Missing"
Else
MsgBox "Finished"
End If
End Sub

 

[JMCN]

Sub Finish_Click()
Dim bFail As Boolean
Dim cell As Range

For Each cell In Range("C27:C46,C52:C70")
If cell.Value <> 0 Then
If Not IsDate(cell.Offset(0, -1)) Then
cell.Offset(0, -1).Value = "Missing tx Date"
bFail = True
End If
End If
If cell.Offset(0, 4).Value <> 0 Then
If Not IsDate(cell.Offset(0, 3)) Then
cell.Offset(0, 3).Value = "Missing tx Date"
bFail = True
End If
End If
Next
If bFail Then
MsgBox "Dates are Missing"
Else
MsgBox "Finished"
End If
End Sub

thanks for your help tom! i always get lost in the for next and if
then statements! jung