+ Reply to Thread
Results 1 to 4 of 4

Sum of all Loops - VBA

Hybrid View

Blackhawks Sum of all Loops - VBA 11-04-2016, 01:09 PM
Kenneth Hobson Re: Sum of all Loops - VBA 11-04-2016, 01:36 PM
Arkadi Re: Sum of all Loops - VBA 11-04-2016, 01:41 PM
Blackhawks Re: Sum of all Loops - VBA 11-06-2016, 11:35 AM
  1. #1
    Forum Contributor
    Join Date
    05-29-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    143

    Sum of all Loops - VBA

    Hello,
    I have a program that identifies a product that is being used. If the conditions are met, then it will take the unit cost of each component of the finished good and adds it up and adds it to the finished good - giving the Finished Good it's respective unit cost.
    I'm having a problem adding up the values of the components. How do I get the sum of all of the loops? Here's the code, I included comments on the relevant 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, XX As Range, count As Long, YU As Integer
    Dim Dub As Integer
    
    
       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, 9) = "Yes") Then
             Else
               ' Wk.Cells(1, 9) = "Yes"
                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
                                     XX.Offset(0, 11) = XX.Offset(0, 11).Value - BM.Offset(0, 4).Value
                                     YU = BM.Offset(0, 9) ' Here is the problem code
        ' I want to find the sum of YU and add it to XX.Offset(0, 14)
                                  End If
                               Next
                            End With
                         End If
                      Next
                   End With
                ElseIf (Wk.Cells(1, 6) = "Partial") 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
                                     XX.Offset(0, 11) = XX.Offset(0, 11) - (Wk.Offset(0, 6).Value * BM.Offset(0, 5))
                                  End If
                               Next
                            End With
                         End If
                      Next
                   End With
                End If
             End If
          Next
       End With
    End Sub

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Sum of all Loops - VBA

    Your code is unclear. It would be more clear if you added the .Value property rather than relying on the default property to be used.

    You set the data type for YU to be integer. Maybe you should have used Long or even Double if you have decimal values.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Sum of all Loops - VBA

    At a quick glance... to keep adding to YU, how about instead of
    YU = BM.Offset(0, 9)
    use
    YU = YU + BM.Offset(0, 9)
    As kenneth pointed out, integer has a relatively low maximum (32,767 in Excel 2010, while Long data type goes past 2 billion.), so depending on the total, you may need to change the data type
    Last edited by Arkadi; 11-04-2016 at 01:46 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Forum Contributor
    Join Date
    05-29-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    143

    Re: Sum of all Loops - VBA

    Thanks everyone. Your suggestions helped immensely.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Two For Next Loops together..
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2015, 06:32 AM
  2. [SOLVED] For next loops
    By Boblincs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2013, 01:49 PM
  3. VBA Loops
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2011, 06:58 AM
  4. VBA Loops
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2011, 09:19 AM
  5. Help with Loops
    By manojparmar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2006, 05:28 AM
  6. Loops & Ifs
    By Shorty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-04-2006, 07:10 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