Results 1 to 5 of 5

Error - Do Until with iRows and kRows?

Threaded View

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Ann Arbor, MI
    MS-Off Ver
    2016
    Posts
    96

    Error - Do Until with iRows and kRows?

    Hello! I have created a template Order Form that a user is to fill out. When they are done, they click a "Ship" button. That button opens up a workbook with a list of all of the Shipments placed through clicking that button and adds the new parts being ordered to the list. In addition, the Packing List is saved under its own file with a unique ShipNo.

    The problem is that when the parts are being put into the new list, they are repeating over each other instead of adding to the list. So, if the list already has 3 parts in it and I add 3 more, those parts are overwritten by the new ones. My code is listed below. I placed green comments where I have tried different ideas. Sometimes it would add only one part to the list, sometimes it would add none. I need it to add all of them. Any help would be greatly appreciated

    
    Sub ShipTest()
        
    Application.ScreenUpdating = False
    
    'This section copies the Packing List and Shipping Document and moves it to a new Workbook created
        Dim ShipNo As Long
        ShipNo = Sheets("2018 Packing List").Cells(3, 11).Value  'this cell is hidden in white ink so that the ShipNo is correct in cell to left of it
        Sheets("2018 Packing List").Cells(3, 11).Value = Sheets("2018 Packing List").Cells(3, 11).Value + 1
        Filenm = Sheets("Order Form").Cells(1, 2).Value 'reference to the filename
        Sheets(Array("2018 Packing List", "Shipping Request Form")).Copy
        ChDir "C:\Users\Z645352\Desktop\Test1\ShippingDoc\"
        ActiveWorkbook.SaveAs FileFormat:=xlOpenXMLWorkbookMacroEnabled, Filename:= _
            "C:\Users\Z645352\Desktop\Test1\ShippingDoc" & Filenm & "2018" & ShipNo & ".xlsm"
        
        ActiveWindow.Close savechanges:=False
        
    
    'This section copies the necessary information from Order Form to Shipments
    Sheets("Order Form").Activate
    OrderNo = Range("B2").Value
    Dim PartNo(100) As String
    Dim Quantity(100) As Integer
    
    Dim iRow As Integer
       iRow = 8 'start one row early or it will skip the first row of the order
       Do Until IsEmpty(Cells(iRow, 1))
       iRow = iRow + 1
       PartNo(iRow) = Cells(iRow, 1).Value
       Quantity(iRow) = Cells(iRow, 5).Value
       Loop
    maxRow = iRow
    iRow = 9 'added this line to stop error of adding extra empty row
    
    'opens up the ship list
    Workbooks.Open Filename:= _
            "C:\Users\Z645352\Desktop\Test1\WorkbookB.xlsm"
    
    'name the kRow
    Sheets("Shipments").Activate
    kRow = 6
    
    '-------------------------THIS IS WHERE THE ERROR OCCURS-----------------------
        Do Until iRow = maxRow
    '   Do Until IsEmpty(Cells(kRow, 1))
    '   iRow = maxRow 'do until the list is done
        
    Cells(kRow, 1).Value = PartNo(iRow)
    Cells(kRow, 4).Value = Quantity(iRow)
    Cells(kRow, 3).Value = Filenm
    Cells(kRow, 2).Select 'add a hyperlink to the cell where the ShipNo appears
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="C:\Users\Z645352\Desktop\Test1\ShippingDoc\" & Filenm & "2018" & ShipNo & ".xlsm", TextToDisplay:=Filenm & "2018" & ShipNo
        
    '    Do Until iRow = iRow + 1 'so that the code will not continue to rewrite in the same row over and over again
         iRow = iRow + 1
         kRow = kRow + 1
        Loop
    '---------------------------------end of errors-----------------------------------
        
    ActiveWorkbook.Save
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    
    End Sub
    As of right now, is still also overwriting previous rows in the list. I've added an example order form and shipments list as attachments for help. Any ideas?
    Attached Files Attached Files
    Last edited by whahmira; 08-29-2018 at 08:24 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Odd Error Handling Behaviour; goes to error handler even when no error?
    By kalikj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2018, 10:04 AM
  2. [SOLVED] Run-tim error -2147467259(80004005): Automation error Unspecidied error
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2014, 12:12 PM
  3. Excel macro (compile error. syntax error.) error
    By salar_younis in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2014, 06:11 AM
  4. Receiving following error “Complie error : syntax error” Help
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2012, 10:19 AM
  5. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  6. Error 75 File/Path access error, sometimes Error 1004
    By smokebreak in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-16-2011, 02:35 PM
  7. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1