Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: Sum in selected cell
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Sum in selected cell

    Also should the 2nd line of your data be 0.5 rather than 1.5?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Basically the account handlers put that info into the sheet & then I go in after and change the estimated hours they have entered to the correct amount (according to that little formula I use) & then alter the figure in the hours column to the correct amount. Which is why I was originally asking about a Macro that I can just select the hours cell & it runs my formula I do on the calculator (order qty + 10%) / number up / 7000 =) But I now realise the number up bit might be confusing the way we write it so maybe I'd have to manually enter that number.

    Not sure if that answers your question, dont like taking up too much of your time, its really hard to explain & I'm not very good at describing so no worries in you wanna give up lol

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Sum in selected cell

    How about
    Code:
    Sub leaper1981()
       Dim Cl As Range
       Dim Sp As Variant
       For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
          Sp = Split(Cl.Offset(, 14), "x")
          Cl.Offset(, 3).Value = Application.Ceiling(Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / 7000, 0.5)
       Next Cl
    End Sub
    This assumes you have a header in row 1, with data starting in A2
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Hi,

    You are right the second like should be 0.5, what I have do after is add more on as I knew this job was more difficult, sometimes I do have to manually increase it due to knowledge of the jobs etc. But yes you're right I would expect the formula to have spat out 0.5

  5. #15
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub leaper1981()
       Dim Cl As Range
       Dim Sp As Variant
       For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
          Sp = Split(Cl.Offset(, 14), "x")
          Cl.Offset(, 3).Value = Application.Ceiling(Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / 7000, 0.5)
       Next Cl
    End Sub
    This assumes you have a header in row 1, with data starting in A2

    Thanks again, I will try this very shortly

  6. #16
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Hi Again,

    pest here lol, It didnt work which is obviously due to my lack of explanation (shame you cant attach the actual sheet) I have listed the rows and columns they start from. But I would say the colum will always be the same but rows can change which may be one of the reasons why I was looking at being able to just select the cell I want to change and then running the macro for that specific one (if that makes sense) I'm probably not getting it across well but in my head it would be something like clicking on say D7 then pressing a button I create for the macro & it does that little sum of order qty + 10% / number up / 7000 =...........and then that cell would display the figure. Off home now but if you get a chance to look great & I'll be back on it Monday. Thanks again.

    ABCDEFGHIJKLMNOPQRST
    4DayTimeJob noHoursCustomerCust Ord NoProduct Code / TitleQuantityRep/NewPrice/1000Cutter NoStyleCyl SizeBlank SizeNo UpColoursBoardMetersOrdValStatus - Delivery Date
    5Mon07.00amMAINTENANCE
    6Mon08.00am302981.5CATPHA102120 EO288459 10 CAV BC (A11297)15000NEW353.00F2341Unglued Wallet10161x2142x1N/A420413PE21725,295.00RACK A co all del 5/9 - appertures need removing
    7Mon09:30am302921.5CATPHA101506 EO288262 3X BT (A11213)2000NEW716.00F2338RTE with Fitment16321.7x390.251x1LSGVFBB54037011351,432.00RACK A co all del 6/9 - appertures need removing
    8Mon11:00am302931CATPHA101507 EO288263 INNER CT BX (A11214)800NEW1,024.00F2339CRASHLOCK25356.6x624.251x1LSGVFBB540423784819.20RACK A co all del 6/9 - appertures need removing
    9Mon12:00pm302911.5TORBAYPH36268Y4268JS/1 Amargine MS Carton1000REP626.53F2143RTE14242x344.251x1K,C,396,LSGVFBB440318684626.53RACK A co all del 22/08
    10Mon01:30pm302942TORBAYPH362683416NS01 Morphine Sulfate 2mg in 1mL15000NEW58.90F2082RTE17207x196.252x2K,7409,364,LSGVFBB4404232247883.50RACK A co all del 22/08
    11Mon03:30pm301362LALKIRPO028603CT001285 Man Cave Beard Oil 50ml17200REP66.30F1155RTE13208X152.252X2K,2322,SMMV,FBGLFBB45042319081,140.36RACK A co all del 9/9
    12Mon05.30pm303202THOCAP140757LA750-4 Vitace Vitamin C Carton20000REP75.69F2150RTE19213x230.252x2K,M,Y,1665,349,LSGVFBB45042332461,513.80RACK A co all del 30/10
    13Mon07.30pm303132CROVETPA330658DEC204A Dectospot 500ml Ireland4000REP446.00F1069RTE14346x334.51x1K,C,M,Y,Dectospot Red 186,LSGVFBB50039319611,784.00RACK A co all del 3/9
    14Tue07.00am302422.5PROBIOP108112P10212-02(PSB-005) Restore Vortexin 16x1g10000NEW105.00F1591OTE20226x238.251x2K,Restore Orange,M,487,3415,UVSILK48530532461,050.00RACK A co all del 23/8
    15Tue09:30am302742.5AMIMEDPO 2308S0295-3v Maxalt Melt 10mg20000AMD71.67F1651B1RTE11182X252.252X1K,301,LSGVFBB40036534631,433.40RACK A co all del 28/8
    16Tue12:00pm303102ADVMED202354110011283 Activheal HCD Foam 5x7.54000REP241.08F1035RTE12244X276.251X1K,151,LSGVFBB5403111652964.32RACK A co all del 4/9
    17Tue02:00pm303212ADVMED202356810014575 Melgisorb USA Silver Alg 5x55000REP197.71F1035RTE12244X276.251X1361 Tone,CG10,361 Line,LSGVFBB5403112028988.55RACK A co all del 6/9
    18Tue04:00pm303266ADVMED202357510009260 Maxorb Extra Alg 4x429000REP126.49F1036RTE16270X376.251x1KTB,C,M,Y,MDG362,MLG359,MB,LSGVFBB540311139283,668.21RACK A co all del 29/08
    19Wed07.00am303271.5ADVMED202357510010754 Maxorb Extra Ag 6x63000REP521.41F1109RTE18279x4451x1KTB,C,M,Y,MDG362,877,MB,LSGVFBB54031119521,564.23RACK A co all del 29/08
    20Wed08.30am303162ADVMED202354110013026 Cardinal Calcium Alg 4 x 4.753000REP390.20F1020RTE16283X3961X1K Tone,K Line,2725 Opaque,185,LSGVFBB54031117021,170.60RACK A co all del 6/9
    21Wed10:30am303125ADVMED202354110012160 Silvercel 10x20 Europe19000REP127.12F1071RTE15370X3511X1K,CG9 10%,219,CG9,SVFBB54042386252,415.28RACK A co all del 4/9
    22Wed03:30pm303152ADVMED202354110013024 Cardinal Essentials 6x63000REP475.00F1291G2RTE24386X576.251X1K,CG6,7516,1505,185,LSGVFBB54042324281,425.00RACK A co all del 6/9
    23Wed05.30pm303142ADVMED202354110013020 Cardinal Essentials 4x43000REP452.60F1173RTE19326.8X476.51X1K,CG6,7516,1505,185,LSGVFBB54037020781,357.80RACK A co all del 6/9
    24Wed07.30pm302962NUTGRO59106CAR91607 Original Hair Support Vits 60s France5800NEW164.85F1603RTE10243x220.251x11895,Green Opaque 318,Opaque Grey 425,SMVFBB4503181800956.13wait app co all del 28/8
    25Wed09.30pm303022PROBIOP108183P10093-01 (PSB-021) Pro-Kolin+ 60ml TVM6000REP211.76F1818OTE18363X216.251X2K,C,M,Y,CG8,7459,7704,LSGVINV43541319521,270.56RACK A co all del 27/8 - ON HOLD
    26Thur08.30am302992PROBIOP108183P10019-02 (PSB-047) Synbiotec D-C 50 Cap TVM4000REP299.99F2059OTE20212X236.251X2K,C,M,Y,CG8,7459,7704,LSGVINV43530515431,199.96RACK A co all del 27/8
    27Thur10:30am303002PROBIOP108183P10091-01(PSB-052) Pro-Kolin+ 15ml11000REP104.00F2181OTE12271X136.251X2K,C,M,Y,CG8,7459,7704,LSGVINV43530523031,144.00RACK A co all del 27/8 - ON HOLD
    28Thur12.30pm303012PROBIOP108183P10092-01(PSB-020) Pro-Kolin+ 30ml TVM11000REP120.00F1802OTE15317X172.251X2K,C,M,Y,CG8,7459,7704,LSGVINV43536527851,320.00RACK A co all del 27/8 - ON HOLD
    29Thur02:30pm300728LALKIRPO028366CT001045 Acatar Control 15ml187000REP33.95F2006B1RTE15213X168.252X2C,280,LSGVFBB400423193686,348.65RACK A co all del 9/9
    30Fri07.30am302762LALKIRPO029262CT001044 Acatar Control 15ml16000NEW88.71F2006B1RTE15213x168.252x2C,280,LSGVFBB40042318861,419.36await app co all del 9/9
    31
    32
    33
    34PRINT & DIE CUT PLANNING SCHEDULEIncarda ExelENSOCOATSILK
    35Mon - Fri - Double DaysPerforma CreamINVERCOTE G
    36G2ArktikaKORSNAS
    37DayTimeJob noHoursCustomerCust Ord NoProduct Code / TitleQuantityRep/NewPrice/1000Cutter NoStyleCyl SizeBlank SizeNo UpColoursBoardMetersOrdValStatus - Delivery Date
    38Mon07.00amMAINTENANCE
    39Mon12:00pm303222PROBIOP108203P10025-05(PSB-005) Bio-Kult Infantis 16x1g7000REP132.46F1805OTE10226X238.251X1K,Neat Cyan,M,336,1585,LSGVINV4353052370927.22RACK A co all del 29/8
    40Mon02:00pm303231.5PROBIOP108203P10206-01(PSB-005) Protexin Restore 16 PH1167-C2000REP423.00F1805OTE10226X238.251X1K,Protexin Pink,487,1585,3415,7606,LSGVINV435305934846.00RACK A co all del 29/8
    41Mon03:30pm303043.5ADVMED202354010013156 Trofolastin 5x7.516000REP104.01F1032RTE10234X2461X1K,8483,877,LSGVFBB54031149491,664.16RACK A co all del 27/8
    42Mon07.00pm303192THOCAP140757LA631-1 Viterra Mulher Platinum 55+9600REP180.00F1439E1RTE10192x200.252x1OW,C,M,Y,326,Viterra Blue,OW,LSGVFOIL41541316371,728.00RACK A co all del 22/10
    43Mon09.30pm302954HERCOSPO00049542SEC00445 Viviscal Densifying Oil Elixar GL17500REP252.00F2176RTE Fitment13253.4x310.251x1OW Tint Mix, Viviscal Red, OW, K, K, LSGVFOIL41536069704,410.00RACK A co all del 23/9
    4452,765.82

    Data



    Last edited by Fluff; Aug 16th, 2019 at 12:37 PM.

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Sum in selected cell

    Try
    Code:
    Sub leaper1981()
       Dim Cl As Range
       Dim Sp As Variant
       For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))
          If IsNumeric(Cl.Offset(, 2)) And Cl.Offset(, 2) <> "" Then
             Sp = Split(LCase(Cl.Offset(, 14)), "x")
             Cl.Offset(, 3).Value = Application.Ceiling(Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / 7000, 0.5)
          End If
       Next Cl
    End Sub
    Also when posting data, please do not post hundreds of blanks rows. It can slow the site down & is totally unneeded.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Hi Fluff,

    Sorry for the long post didn't realise it copied all that. I'll make a note to check that next time I post. I will try this today thanks again for your help.

  9. #19
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Hi again,

    That seemed to work perfect. (As Columbo would say).......just one more thing lol. Should of mentioned this before but originally I thought the only way to do this would be to have 2 separate buttons but maybe not looking at what you've managed to do! There is a column which says (cylinder size) Basically if the Cylinder size is between 10 - 17.....the sum is order qty + 10% divided by the number up, divided by 7000 + .5 (as looks like what you've done). But if the Cylinder size is between 18 - 25....the sum is order qty + 10% divided by the number up, divided by 5000 + .5

    Would it be possible to incorporate both sums into the Macro?

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Sum in selected cell

    Ok, how about
    Code:
    Sub leaper1981()
       Dim Cl As Range
       Dim Sp As Variant
       For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))
          If IsNumeric(Cl.Offset(, 2)) And Cl.Offset(, 2) <> "" Then
             Sp = Split(LCase(Cl.Offset(, 14)), "x")
             Cl.Offset(, 3).Value = Application.Ceiling(Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000), 0.5)
          End If
       Next Cl
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •