Problem with loop

B

Brad

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
 
T

Tom Ogilvy

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
 
B

Brad

Thanks Tom, that did the trick!

Tom Ogilvy said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top