Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 97: if then and for next loops help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 97: if then and for next loops help
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 -- Regards, Tom Ogilvy JMCN wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 97: if then and for next loops help
"Tom Ogilvy" wrote in message 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 thanks for your help tom! i always get lost in the for next and if then statements! jung |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if function in excel should 12 loops | Excel Discussion (Misc queries) | |||
Loops... | Excel Discussion (Misc queries) | |||
do loops | Excel Worksheet Functions | |||
For next loops | Excel Discussion (Misc queries) | |||
Using For - Next Loops in VB | New Users to Excel |