Results 1 to 4 of 4

If value = 0 then do not add iRow to table?

Threaded View

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

    If value = 0 then do not add iRow to table?

    Hello! I have a "Ship" button with a macro that copies filled out rows in an order form and adds them to a list of shipments in another workbook.
    In Workbook A, there is a table of part numbers and their quantities, referred to in the code as iRow. Sometimes I have to make partial shipments, so I will fill out the order form to have a quantity of "0" on the part numbers that will not be shipped in that particular order.
    I would like to create a macro that senses if there is a quantity of 0, that specific iRow does not get transferred to the shipments list.
    The shipments list is referred to as kRow in the macro. Below you can see the code, and attached is the test workbook for additional reference.

    'This section copies the necessary information from Order Overview to Shipments List
    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"
    
    'naming a kRow 
       With Sheets("Shipments")
            kRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 'finds first empty row so that the code will not continue to rewrite in the same row over and over again
        End With
        Do Until iRow = maxRow
    '-------I was thinking to insert the If statement here, but didn't really know how to do it for iRows and kRows-------------
    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
        
         iRow = iRow + 1
         kRow = kRow + 1
        Loop
        
    ActiveWorkbook.Save
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub
    Any help would be greatly appreciated
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with Damage Record code (irow)
    By drabestuk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2017, 09:22 AM
  2. [SOLVED] Make IRow = 1000 dynamic
    By markDuffy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-11-2017, 12:03 PM
  3. Creating IF statement for iRow Entry
    By excelenergy in forum Excel General
    Replies: 8
    Last Post: 09-29-2014, 04:26 PM
  4. Combinations and iRow formula
    By Fitz1567 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2013, 12:13 AM
  5. [SOLVED] Issue using iRow
    By faoltaem in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2012, 03:00 AM
  6. Define "iRow" as Row displayed after data is filtered
    By mgdmeyers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-22-2012, 10:41 AM
  7. [SOLVED] Arranging data:Dim iRow As Long
    By vijaya in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2005, 12:45 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