+ Reply to Thread
Results 1 to 13 of 13

Macro To Count Data With Exeptions

Hybrid View

  1. 03-02-2010, 12:43 PM #1
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Macro To Count Data With Exeptions

    Hi

    Could someone please have a look at this, I have attached a sample and will try and explain what I would like. I am not sure if a macro will do this or maybe some other way

    In B3 I have a number that calculates how many full pallets I have, this is done by looking down columns J, M, P, S, V, Y, AB & AE , Each column is a different week 1 – 8.

    D Column = Layer
    E Column = Full Pallet

    In the sample file you will see that in J5 I have ordered 11 which is 1 layer as seen in D5, we know that in E5 the full pallet is 77 so this is 1/7th of a pallet

    In J8 I have ordered 16, again if we look at D8 you will see this is 1 layer and in E8 the full pallet is 160, so this is 1/10th of a pallet

    This continues all the way down the sheet and B3 should read something like 2.72 pallets

    The problem I have is when data is put into the next column which is M column (week2) I would like it to stop looking at the J column (week1) and just give me the results of M, and so on right through to AE column (week8)

    Is this possible? Thanks very much in advance for any help.
    Attached Files Attached Files
    Last edited by JimmiOO; 03-03-2010 at 10:20 AM.
    Reply With QuoteRegister To Reply
  2. 03-02-2010, 12:52 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro To Count Data With Exeptions

    Maybe something ugly but doable like this...D5:

    =IF(AE5>"",AE5,IF(AB5>"",AB5,IF(Y5>"",Y5,IF(V5>"",V5,IF(S5>"",S5,IF(P5>"",P5,IF(M5>"",M5,J5)))))))

    That formula starts at the last week and works backward to display the value in furthest column out. Excel 2003 has a limit of 7 IF() statements, so we've maxxed it here, but we made it.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-02-2010, 01:04 PM #3
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro To Count Data With Exeptions

    In F5 perhaps:
    =D5/E5 ...formatted as a percentage, then copied down to the other rows with data.

    Then in B3:
    =SUM(F:F) formatted as General.
    Reply With QuoteRegister To Reply
  4. 03-03-2010, 05:06 AM #4
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Count Data With Exeptions

    Hi, i have tried that but it does not seem to be working for me. Would it be easier to create a macro with maybe a calculate button when clicked would give me the result in B3?
    Reply With QuoteRegister To Reply
  5. 03-03-2010, 05:25 AM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro To Count Data With Exeptions

    Sample attached...macros are a poor substitute for simple worksheet formulas.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-03-2010, 06:47 AM #6
    excelxx
    excelxx is offline
    Forum Contributor
    Join Date
    02-04-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Office 2007 (home) Office 365 (work)
    Posts
    134

    Re: Macro To Count Data With Exeptions

    Hi JimmiOO

    Here is a possible macro alternative, which may be usable

    Sub PalletCalculation()
    Dim nRow As Integer: Dim nCol As Integer
    Dim wsStock As Worksheet
    Set wsStock = Worksheets("STOCK")
    For nRow = 5 To wsStock.UsedRange.Rows.Count Step 3    'starting from 5th row
        For nCol = 10 To wsStock.UsedRange.Columns.Count Step 3     'starting from 10th column
            Order = wsStock.Cells(nRow, nCol)
            Pallet = wsStock.Cells(nRow, 5)
            If Pallet = 0 Then Exit For
            If Order = 0 Then
                CumPallet = CumPallet + Round(PrevOrder / Pallet, 2)
                Exit For
            Else
                PrevOrder = Order
            End If
        Next nCol
    Next nRow
    Range("B3").Value = CumPallet
End Sub
    Reply With QuoteRegister To Reply
  7. 03-03-2010, 06:57 AM #7
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Count Data With Exeptions

    Hi i have tried this out but when i put data into the second week M column, the formula in the D column stays the same.
    Reply With QuoteRegister To Reply
  8. 03-03-2010, 07:02 AM #8
    excelxx
    excelxx is offline
    Forum Contributor
    Join Date
    02-04-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Office 2007 (home) Office 365 (work)
    Posts
    134

    Re: Macro To Count Data With Exeptions

    Hi

    I actually didn't do anything with column D, is column D equal to the order for the current week or last order.

    Thanks
    Reply With QuoteRegister To Reply
  9. 03-03-2010, 07:14 AM #9
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Count Data With Exeptions

    sorry excelxx i was talking to JBeaucaire there, i have just seen your post and tried your macro and as usual it works a treat, it would be handy if it would calculate without having to hit the button though. Column D is only the layer quantity and should never change, with JBeaucaire formula i guess i would have to add another column then hide it.
    Last edited by JimmiOO; 03-03-2010 at 07:17 AM.
    Reply With QuoteRegister To Reply
  10. 03-03-2010, 07:40 AM #10
    excelxx
    excelxx is offline
    Forum Contributor
    Join Date
    02-04-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Office 2007 (home) Office 365 (work)
    Posts
    134

    Re: Macro To Count Data With Exeptions

    Hi JimmiOO

    The best I could come up with, is for the calculation to be made just before the workbook is saved.

    I tried executing the calculation when the worksheet is changed, but for some reason it seems to run forever, so I abandoned that idea.
    Last edited by excelxx; 03-04-2010 at 08:22 AM.
    Reply With QuoteRegister To Reply
  11. 03-03-2010, 08:53 AM #11
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Count Data With Exeptions

    Hi excelxx think i will go with the button version, but think i have just found a problem, when i put in a 0 it throws the calculation out, as not every line will have number greater that 0 that will be a problem
    Reply With QuoteRegister To Reply
  12. 03-03-2010, 09:30 AM #12
    excelxx
    excelxx is offline
    Forum Contributor
    Join Date
    02-04-2010
    Location
    Hertfordshire, England
    MS-Off Ver
    Office 2007 (home) Office 365 (work)
    Posts
    134

    Re: Macro To Count Data With Exeptions

    Hi JimmiOO

    The code previously ignored the value of 0 in the order for a week. But I have amended the code to accept 0 as a possible order value. I'm not sure if that is what you want.
    Last edited by excelxx; 03-04-2010 at 08:22 AM.
    Reply With QuoteRegister To Reply
  13. 03-03-2010, 10:20 AM #13
    JimmiOO
    JimmiOO is offline
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro To Count Data With Exeptions

    Thats exactly what i need, thanks again excelxx for all your great help. Problem Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1