Results 1 to 4 of 4

VBA Script not working as Expected

Threaded View

Blackhawks VBA Script not working as... 11-01-2016, 08:12 PM
Norie Re: VBA Script not working as... 11-01-2016, 08:24 PM
Blackhawks Re: VBA Script not working as... 11-01-2016, 08:29 PM
Blackhawks Re: VBA Script not working as... 11-01-2016, 09:01 PM
  1. #1
    Forum Contributor
    Join Date
    05-29-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    143

    VBA Script not working as Expected

    Hello,

    For some reason the code below works, except for on the loop. I've included the workbook that I'm working on, the module name is WIP_Complete. It's supposed to subtract the quantity from the Item Master list from the quantity used in the Inventory Allocation tab for the respective component (e.g., Dried Hibiscus). I have it working except it gets the wrong output cell during the last loop. Rather than outputting in the correct cell, it does so 1 below and I have no idea why.


    Here's the code:

    
    
    Option Explicit
    
    Sub Invo()
    Dim InvAllocWS As Worksheet
    Dim BillMatWS As Worksheet
    Dim ItemMas As Worksheet
    Dim WorkProWS As Worksheet
    Dim Wk  As Range, BM  As Range, CP As Range, XX As Range
    
    
       Set InvAllocWS = Worksheets("Inventory Allocation")
       Set ItemMas = Worksheets("Item Master")
       Set BillMatWS = Worksheets("Bill of Material")
       Set WorkProWS = Worksheets("Work in Process")
    
       With WorkProWS
          For Each Wk In Range(.Cells(3, 4), .Cells(Rows.Count, 4).End(3))
             If (Wk.Cells(1, 6) = "Yes") Then
                With InvAllocWS
                   For Each BM In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(3))
                      If (BM = Wk) Then
                         With ItemMas
                            For Each XX In Range(.Cells(3, 4), .Cells(Rows.Count, 4).End(3))
                               If (XX = BM.Cells(1, 4)) Then
        MsgBox XX.Offset(1, 11).Value
                                  XX.Offset(1, 11) = XX.Offset(1, 11) - BM.Offset(0, 4).Value
                               End If
                            Next
                         End With
                      End If
                   Next
                End With
             End If
          Next
       End With
    End Sub
    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. [SOLVED] filldown not working as expected
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2016, 10:50 AM
  2. [SOLVED] Formula not working as expected
    By nalamarmite1 in forum Excel General
    Replies: 4
    Last Post: 03-08-2016, 08:34 AM
  3. [SOLVED] IF/ELSEIF not working as expected
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2015, 10:00 AM
  4. [SOLVED] CountIfS not working as expected
    By rschoenb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-12-2014, 11:45 PM
  5. ISNA not working as expected
    By micco in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 01:54 PM
  6. [SOLVED] IF statement is not working as expected?
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2009, 02:02 PM
  7. IF statement not working as expected?
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2009, 07:03 AM

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