I can't get my loop to stop, please help!

B

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
I have written a loop to go down a range of codes one by one, copying and pasting each active cell into another worksheet, printing that worksheet and repeating the process for the next code down in the list theoretically until the bottom cell which contains the word END, where I want my macro to stop.

Despite my variable being assigned and in VBA showing that it recognises the value to be "END" when the active cell reaches the bottom cell the macro is not stopping and carries on going into the blank cells.

Can anybody see where I've gone wrong? As far as I can see I've assigned the variables and given a condition for the loop to stop and am at a loss to why the thing keeps going once it reached the "END" cell.

Code below, any advice or thoughts greatly appreciated.
Code: 
Sub PrintISINPage()

    Dim myWorkbook As Workbook
    Dim wsISINList As Worksheet
    Dim wsISINToPrint As Worksheet
    Dim ISINPrint As Range
    Dim myCommand As String

    Set myWorkbook = ActiveWorkbook
    Set wsISINList = Sheets("ISINList")
    Set wsISINToPrint = Sheets("ISINToPrint")
    Set ISINPrint = Range("ISINPrint")
    
    myCommand = ActiveCell.Value()
    
    wsISINList.Select
    Range("A1").Select
    Selection.Copy
    wsISINToPrint.Select
    ISINPrint.Select
    Selection.PasteSpecial Paste:=xlAll
    wsISINToPrint.PrintOut
    Sheets("ISINList").Select

    Do Until myCommand = "END"
        wsISINList.Select
        ActiveCell.Offset(1).Activate
        Selection.Copy
        wsISINToPrint.Select
        ISINPrint.Select
        Selection.PasteSpecial Paste:=xlAll
        wsISINToPrint.PrintOut
        wsISINList.Select
        ActiveCell.Activate

    Loop
End Sub

 
Sort by date Sort by votes
Is it?

Code: 
Do Until myCommand = "END"
        wsISINList.Select
        ActiveCell.Offset(1).Activate
        myCommand = ActiveCell.Value
        Selection.Copy
        wsISINToPrint.Select
        ISINPrint.Select
        Selection.PasteSpecial Paste:=xlAll
        wsISINToPrint.PrintOut
        wsISINList.Select
        ActiveCell.Activate

    Loop
 
Upvote 0
2 things, this:
Code: 
  myCommand = ActiveCell.Value()
should be this
Code: 
  myCommand = ActiveCell.Value
And you might want to put an
Code: 
Option compare text
statement at the top of the code (outside the "sub"). That ensures it won't get hung up on possible discrepancies in upper/lower case.
 
Upvote 0
I am hoping to combine the loop with another loop concerning the printing of the contents of a folder of PDF documents. I basically want so print an excel sheet, then print a PDF, then an excel and so on...

I am struggling to bring the two loops together (as shown in green and red) and am starting to wonder if it's at all possible (in the code below they are not linked at all just one after the other). Unfortunately as I need to dip into both loops before either has completed so it's not simply a case of nesting one inside the other.

So firstly... am I wasting my time?
Secondly, if not how would you experts alter the below code to combine the loops so that I can print an excel sheet containing the information derived from cell A1 in a list, and then the first PDF in the PDF Folder, then A2 and the second PDF, and so on?

Any suggestions warmly appreciated. Thanks for reading.

Jon

Code: 
Sub PrintISINPage()

    Dim myWorkbook As Workbook
    Dim wsISINList As Worksheet
    Dim wsISINToPrint As Worksheet
    Dim ISINPrint As Range
    Dim myCommand As String
    Dim fso, fld, file

    Set myWorkbook = ActiveWorkbook
    Set wsISINList = Sheets("ISINList")
    Set wsISINToPrint = Sheets("ISINToPrint")
    Set ISINPrint = Range("ISINPrint")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FILE_PATH)
    
    Const ADOBEPATH As String = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe"
    Const FILE_PATH As String = "S:\Data Dept\Excel Widgets\CreditSuisseQuickPrintTempFiles"
    Const FILE_EXT As String = "PDF"
   
    wsISINList.Select
    Range("A1").Select
    Selection.Copy
    wsISINToPrint.Select
    ISINPrint.Select
    Selection.PasteSpecial Paste:=xlAll
    wsISINToPrint.PrintOut
    Sheets("ISINList").Select
    
OPENS ALL PDFS IN FOLDER
    For Each file In fld.Files
        If UCase(Right(file.Name, 3)) = FILE_EXT Then
            Shell """" & ADOBEPATH & """ /o """ & file.Path & """"
        End If
    Next
    
[COLOR=Red]PRINTS ALL PDFS IN FOLDER[/COLOR]
[COLOR=Red]    For Each file In fld.Files
         If UCase(Right(file.Name, 3)) = FILE_EXT Then
            Shell """" & ADOBEPATH & """ /t """ & file.Path & """"
        End If
    Next[/COLOR]

[COLOR=Lime]MOVES VALUE FROM A1 TO OTHER WORKSHEET AND PRINTS
CARRIES ON TO A2 AND SO ON UNTIL END   
    Do Until myCommand = "END"
        wsISINList.Select
        ActiveCell.Offset(1).Activate
        myCommand = ActiveCell.Value
        Selection.Copy
        wsISINToPrint.Select
        ISINPrint.Select
        Selection.PasteSpecial Paste:=xlAll
        wsISINToPrint.PrintOut
        wsISINList.Select
        ActiveCell.Activate
   Loop[/COLOR]
    
End Sub
 
Upvote 0
Please ignore above code I can do it only using the For Each structure.

As a general enquiry though is it possible to switch between loops?
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
How to make loop stop when the cell is empty? (VBA)
Replies
3
Views
382
kvsrinivasamurthy
K
C
  • Solved
Help! Can't pass a range of cell strings into a variant array/can't reference the strings in this array - myarray(I,1) gives subscript error
Replies
3
Views
181
Peter_SSs
Peter_SSs
P
  • Question Question
For Each Loop with copy and paste over multiple worksheets
Replies
1
Views
272
Sam_D_Ben
Sam_D_Ben
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
523
MARK858
MARK858
M
  • Solved
I can't get copy from one worksheet to another to work
Replies
4
Views
360
MARK858
MARK858

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top