Results 1 to 8 of 8

inventory update macro will stop on empty row and out of sequence produce id entry

Threaded View

  1. #1
    Registered User
    Join Date
    10-09-2016
    Location
    los angeles
    MS-Off Ver
    2016
    Posts
    7

    inventory update macro will stop on empty row and out of sequence produce id entry

    couple problems with this macro: A) random Product ID entry on invoice will stop the macro from running after the first inventory update. Product must be listed on the invoice in the same order as iit's listed on the product sheet.
    B) Macro stops on empty row.

    I need this macro to run regardless of entry order and ignore empty cell and run to the last row.
    I have very limited VBA skill, I would really appreciate any help.
    Thank you.
    
    Sub updateInventory()
    Dim x As Long
    Dim r As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim newQuantity As Long
    Dim answer As Integer
    
    Set ws1 = Worksheets("Invoice")
    Set ws2 = Worksheets("Inventory")
    
    answer = MsgBox("Are you sure you want to update Inventory?", vbYesNo + vbQuestion, "Empty Sheet")
    x = 19
    r = 2
        If answer = vbYes Then
            For Z = 1 To 14
                    If ws1.Range("C" & x).Value = ws2.Range("B" & r).Value Then
                        newQuantity = ws2.Range("C" & r).Value - ws1.Range("E" & x).Value
                        ws2.Range("c" & r).Value = newQuantity
                        
                        x = x + 1
                    Else
                        r = r + 1
                    End If
            Next Z
            x = x + 1
           
        Else
        End If
        
    End Sub
    Attached Files Attached Files
    Last edited by 6StringJazzer; 10-12-2016 at 12:03 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do you fill a series of cell references and skip four to produce a sequence
    By john dalton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2014, 07:25 AM
  2. [SOLVED] Need a macro that will stop autofill once it finds an empty cell (row).
    By SCACCHIA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2014, 06:02 PM
  3. Inventory Update Macro
    By timms1030 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2014, 05:38 PM
  4. Inventory Update Macro
    By Fredbugatti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2014, 03:42 AM
  5. I need to stop the loop to an empty cell on this email generating macro
    By Neowise25 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-27-2014, 04:38 PM
  6. Macro to INSERT a STRING in SEQUENCE or as the LAST entry
    By JamesGoulding85 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-20-2013, 09:04 AM
  7. Stop update formulas when macro starts then update when complete
    By ruperupe in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2012, 02:42 PM

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