Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with loop
I'm new with loops and I can not seem to exit this loop. The loop works in
notifying the user there is no more data (by way of msgbox). But when I click Okay on the box, it just stays there. So I'm thinking the loop is not exiting properly. Can you help me exit this loop. Thanks. Sub printfromqueue() Dim usrid As String Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim cell As Range usrid = Environ("Username") Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1") Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)) For Each cell In rng If cell.Value < "" Then Set bk = Workbooks.Open(cell.Value) finalizequeue 'print macro bk.Close savechanges:=False Else: MsgBox ("no more data"), vbOKOnly End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with loop
Actually, you should never process a cell that is empty. So assume there is
a null string in some of your cells. Sub printfromqueue() Dim usrid As String Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim cell As Range usrid = Environ("Username") Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1") Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)) For Each cell In rng If cell.Value < "" Then Set bk = Workbooks.Open(cell.Value) finalizequeue 'print macro bk.Close savechanges:=False Else MsgBox ("no more data"), vbOKOnly exit For End If Next End Sub -- Regards, Tom Ogilvy "Brad" wrote in message ... I'm new with loops and I can not seem to exit this loop. The loop works in notifying the user there is no more data (by way of msgbox). But when I click Okay on the box, it just stays there. So I'm thinking the loop is not exiting properly. Can you help me exit this loop. Thanks. Sub printfromqueue() Dim usrid As String Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim cell As Range usrid = Environ("Username") Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1") Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)) For Each cell In rng If cell.Value < "" Then Set bk = Workbooks.Open(cell.Value) finalizequeue 'print macro bk.Close savechanges:=False Else: MsgBox ("no more data"), vbOKOnly End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with loop
Thanks Tom, that did the trick!
"Tom Ogilvy" wrote: Actually, you should never process a cell that is empty. So assume there is a null string in some of your cells. Sub printfromqueue() Dim usrid As String Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim cell As Range usrid = Environ("Username") Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1") Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)) For Each cell In rng If cell.Value < "" Then Set bk = Workbooks.Open(cell.Value) finalizequeue 'print macro bk.Close savechanges:=False Else MsgBox ("no more data"), vbOKOnly exit For End If Next End Sub -- Regards, Tom Ogilvy "Brad" wrote in message ... I'm new with loops and I can not seem to exit this loop. The loop works in notifying the user there is no more data (by way of msgbox). But when I click Okay on the box, it just stays there. So I'm thinking the loop is not exiting properly. Can you help me exit this loop. Thanks. Sub printfromqueue() Dim usrid As String Dim sh As Worksheet Dim rng As Range Dim bk As Workbook Dim cell As Range usrid = Environ("Username") Set sh = Workbooks(usrid & ".xls").Worksheets("Sheet1") Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)) For Each cell In rng If cell.Value < "" Then Set bk = Workbooks.Open(cell.Value) finalizequeue 'print macro bk.Close savechanges:=False Else: MsgBox ("no more data"), vbOKOnly End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do while loop problem | Excel Discussion (Misc queries) | |||
Problem with Do Loop and If | Excel Programming | |||
For Next loop problem | Excel Programming | |||
Loop Problem | Excel Programming | |||
For..Next loop problem | Excel Programming |