Sum in selected cell

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,567
Office Version
365
Platform
Windows
Also should the 2nd line of your data be 0.5 rather than 1.5?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,567
Office Version
365
Platform
Windows
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
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Day</td><td style=";">Time</td><td style=";">Job no</td><td style=";">Hours</td><td style=";">Customer</td><td style=";">Cust Ord No</td><td style=";">Product Code / Title</td><td style=";">Quantity</td><td style=";">Rep/New</td><td style=";">Price/1000</td><td style=";">Cutter No</td><td style=";">Style</td><td style=";">Cyl Size</td><td style=";">Blank Size</td><td style=";">No Up</td><td style=";">Colours</td><td style=";">Board</td><td style=";">Meters</td><td style=";">OrdVal</td><td style=";">Status - Delivery Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Mon</td><td style=";">07.00am</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">MAINTENANCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Mon</td><td style=";">08.00am</td><td style="text-align: right;;">30298</td><td style="text-align: right;;">1.5</td><td style=";">CATPHA</td><td style=";">102120 EO</td><td style=";">288459 10 CAV BC (A11297)</td><td style="text-align: right;;">15000</td><td style=";">NEW</td><td style="text-align: right;;">£353.00</td><td style=";">F2341</td><td style=";">Unglued Wallet</td><td style="text-align: right;;">10</td><td style=";">161x214</td><td style=";">2x1</td><td style=";">N/A</td><td style=";">420413PE</td><td style="text-align: right;;">2172</td><td style="text-align: right;;">£5,295.00</td><td style=";">RACK A co all del 5/9 - appertures need removing</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Mon</td><td style=";">09:30am</td><td style="text-align: right;;">30292</td><td style="text-align: right;;">1.5</td><td style=";">CATPHA</td><td style=";">101506 EO</td><td style=";">288262 3X BT (A11213)</td><td style="text-align: right;;">2000</td><td style=";">NEW</td><td style="text-align: right;;">£716.00</td><td style=";">F2338</td><td style=";">RTE with Fitment</td><td style="text-align: right;;">16</td><td style=";">321.7x390.25</td><td style=";">1x1</td><td style=";">LSGV</td><td style=";">FBB540370</td><td style="text-align: right;;">1135</td><td style="text-align: right;;">£1,432.00</td><td style=";">RACK A co all del 6/9 - appertures need removing</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Mon</td><td style=";">11:00am</td><td style="text-align: right;;">30293</td><td style="text-align: right;;">1</td><td style=";">CATPHA</td><td style=";">101507 EO</td><td style=";">288263 INNER CT BX (A11214)</td><td style="text-align: right;;">800</td><td style=";">NEW</td><td style="text-align: right;;">£1,024.00</td><td style=";">F2339</td><td style=";">CRASHLOCK</td><td style="text-align: right;;">25</td><td style=";">356.6x624.25</td><td style=";">1x1</td><td style=";">LSGV</td><td style=";">FBB540423</td><td style="text-align: right;;">784</td><td style="text-align: right;;">£819.20</td><td style=";">RACK A co all del 6/9 - appertures need removing</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Mon</td><td style=";">12:00pm</td><td style="text-align: right;;">30291</td><td style="text-align: right;;">1.5</td><td style=";">TORBAY</td><td style=";">PH36268</td><td style=";">Y4268JS/1 Amargine MS Carton</td><td style="text-align: right;;">1000</td><td style=";">REP</td><td style="text-align: right;;">£626.53</td><td style=";">F2143</td><td style=";">RTE</td><td style="text-align: right;;">14</td><td style=";">242x344.25</td><td style=";">1x1</td><td style=";">K,C,396,LSGV</td><td style=";">FBB440318</td><td style="text-align: right;;">684</td><td style="text-align: right;;">£626.53</td><td style=";">RACK A co all del 22/08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Mon</td><td style=";">01:30pm</td><td style="text-align: right;;">30294</td><td style="text-align: right;;">2</td><td style=";">TORBAY</td><td style=";">PH36268</td><td style=";">3416NS01 Morphine Sulfate 2mg in 1mL</td><td style="text-align: right;;">15000</td><td style=";">NEW</td><td style="text-align: right;;">£58.90</td><td style=";">F2082</td><td style=";">RTE</td><td style="text-align: right;;">17</td><td style=";">207x196.25</td><td style=";">2x2</td><td style=";">K,7409,364,LSGV</td><td style=";">FBB440423</td><td style="text-align: right;;">2247</td><td style="text-align: right;;">£883.50</td><td style=";">RACK A co all del 22/08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Mon</td><td style=";">03:30pm</td><td style="text-align: right;;">30136</td><td style="text-align: right;;">2</td><td style=";">LALKIR</td><td style=";">PO028603</td><td style=";">CT001285 Man Cave Beard Oil 50ml</td><td style="text-align: right;;">17200</td><td style=";">REP</td><td style="text-align: right;;">£66.30</td><td style=";">F1155</td><td style=";">RTE</td><td style="text-align: right;;">13</td><td style=";">208X152.25</td><td style=";">2X2</td><td style=";">K,2322,SMMV,FBGL</td><td style=";">FBB450423</td><td style="text-align: right;;">1908</td><td style="text-align: right;;">£1,140.36</td><td style=";">RACK A co all del 9/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Mon</td><td style=";">05.30pm</td><td style="text-align: right;;">30320</td><td style="text-align: right;;">2</td><td style=";">THOCAP</td><td style="text-align: right;;">140757</td><td style=";">LA750-4 Vitace Vitamin C Carton</td><td style="text-align: right;;">20000</td><td style=";">REP</td><td style="text-align: right;;">£75.69</td><td style=";">F2150</td><td style=";">RTE</td><td style="text-align: right;;">19</td><td style=";">213x230.25</td><td style=";">2x2</td><td style=";">K,M,Y,1665,349,LSGV</td><td style=";">FBB450423</td><td style="text-align: right;;">3246</td><td style="text-align: right;;">£1,513.80</td><td style=";">RACK A co all del 30/10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Mon</td><td style=";">07.30pm</td><td style="text-align: right;;">30313</td><td style="text-align: right;;">2</td><td style=";">CROVET</td><td style=";">PA33065</td><td style=";">8DEC204A Dectospot 500ml Ireland</td><td style="text-align: right;;">4000</td><td style=";">REP</td><td style="text-align: right;;">£446.00</td><td style=";">F1069</td><td style=";">RTE</td><td style="text-align: right;;">14</td><td style=";">346x334.5</td><td style=";">1x1</td><td style=";">K,C,M,Y,Dectospot Red 186,LSGV</td><td style=";">FBB500393</td><td style="text-align: right;;">1961</td><td style="text-align: right;;">£1,784.00</td><td style=";">RACK A co all del 3/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Tue</td><td style=";">07.00am</td><td style="text-align: right;;">30242</td><td style="text-align: right;;">2.5</td><td style=";">PROBIO</td><td style=";">P108112</td><td style=";">P10212-02(PSB-005) Restore Vortexin 16x1g</td><td style="text-align: right;;">10000</td><td style=";">NEW</td><td style="text-align: right;;">£105.00</td><td style=";">F1591</td><td style=";">OTE</td><td style="text-align: right;;">20</td><td style=";">226x238.25</td><td style=";">1x2</td><td style=";">K,Restore Orange,M,487,3415,UV</td><td style=";">SILK485305</td><td style="text-align: right;;">3246</td><td style="text-align: right;;">£1,050.00</td><td style=";">RACK A co all del 23/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Tue</td><td style=";">09:30am</td><td style="text-align: right;;">30274</td><td style="text-align: right;;">2.5</td><td style=";">AMIMED</td><td style=";">PO 2308</td><td style=";">S0295-3v Maxalt Melt 10mg</td><td style="text-align: right;;">20000</td><td style=";">AMD</td><td style="text-align: right;;">£71.67</td><td style=";">F1651B1</td><td style=";">RTE</td><td style="text-align: right;;">11</td><td style=";">182X252.25</td><td style=";">2X1</td><td style=";">K,301,LSGV</td><td style=";">FBB400365</td><td style="text-align: right;;">3463</td><td style="text-align: right;;">£1,433.40</td><td style=";">RACK A co all del 28/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Tue</td><td style=";">12:00pm</td><td style="text-align: right;;">30310</td><td style="text-align: right;;">2</td><td style=";">ADVMED</td><td style="text-align: right;;">2023541</td><td style=";">10011283 Activheal HCD Foam 5x7.5</td><td style="text-align: right;;">4000</td><td style=";">REP</td><td style="text-align: right;;">£241.08</td><td style=";">F1035</td><td style=";">RTE</td><td style="text-align: right;;">12</td><td style=";">244X276.25</td><td style=";">1X1</td><td style=";">K,151,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">1652</td><td style="text-align: right;;">£964.32</td><td style=";">RACK A co all del 4/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Tue</td><td style=";">02:00pm</td><td style="text-align: right;;">30321</td><td style="text-align: right;;">2</td><td style=";">ADVMED</td><td style="text-align: right;;">2023568</td><td style=";">10014575 Melgisorb USA Silver Alg 5x5</td><td style="text-align: right;;">5000</td><td style=";">REP</td><td style="text-align: right;;">£197.71</td><td style=";">F1035</td><td style=";">RTE</td><td style="text-align: right;;">12</td><td style=";">244X276.25</td><td style=";">1X1</td><td style=";">361 Tone,CG10,361 Line,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">2028</td><td style="text-align: right;;">£988.55</td><td style=";">RACK A co all del 6/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Tue</td><td style=";">04:00pm</td><td style="text-align: right;;">30326</td><td style="text-align: right;;">6</td><td style=";">ADVMED</td><td style="text-align: right;;">2023575</td><td style=";">10009260 Maxorb Extra Alg 4x4</td><td style="text-align: right;;">29000</td><td style=";">REP</td><td style="text-align: right;;">£126.49</td><td style=";">F1036</td><td style=";">RTE</td><td style="text-align: right;;">16</td><td style=";">270X376.25</td><td style=";">1x1</td><td style=";">KTB,C,M,Y,MDG362,MLG359,MB,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">13928</td><td style="text-align: right;;">£3,668.21</td><td style=";">RACK A co all del 29/08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Wed</td><td style=";">07.00am</td><td style="text-align: right;;">30327</td><td style="text-align: right;;">1.5</td><td style=";">ADVMED</td><td style="text-align: right;;">2023575</td><td style=";">10010754 Maxorb Extra Ag 6x6</td><td style="text-align: right;;">3000</td><td style=";">REP</td><td style="text-align: right;;">£521.41</td><td style=";">F1109</td><td style=";">RTE</td><td style="text-align: right;;">18</td><td style=";">279x445</td><td style=";">1x1</td><td style=";">KTB,C,M,Y,MDG362,877,MB,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">1952</td><td style="text-align: right;;">£1,564.23</td><td style=";">RACK A co all del 29/08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Wed</td><td style=";">08.30am</td><td style="text-align: right;;">30316</td><td style="text-align: right;;">2</td><td style=";">ADVMED</td><td style="text-align: right;;">2023541</td><td style=";">10013026 Cardinal Calcium Alg 4 x 4.75</td><td style="text-align: right;;">3000</td><td style=";">REP</td><td style="text-align: right;;">£390.20</td><td style=";">F1020</td><td style=";">RTE</td><td style="text-align: right;;">16</td><td style=";">283X396</td><td style=";">1X1</td><td style=";">K Tone,K Line,2725 Opaque,185,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">1702</td><td style="text-align: right;;">£1,170.60</td><td style=";">RACK A co all del 6/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Wed</td><td style=";">10:30am</td><td style="text-align: right;;">30312</td><td style="text-align: right;;">5</td><td style=";">ADVMED</td><td style="text-align: right;;">2023541</td><td style=";">10012160 Silvercel 10x20 Europe</td><td style="text-align: right;;">19000</td><td style=";">REP</td><td style="text-align: right;;">£127.12</td><td style=";">F1071</td><td style=";">RTE</td><td style="text-align: right;;">15</td><td style=";">370X351</td><td style=";">1X1</td><td style=";">K,CG9 10%,219,CG9,SV</td><td style=";">FBB540423</td><td style="text-align: right;;">8625</td><td style="text-align: right;;">£2,415.28</td><td style=";">RACK A co all del 4/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Wed</td><td style=";">03:30pm</td><td style="text-align: right;;">30315</td><td style="text-align: right;;">2</td><td style=";">ADVMED</td><td style="text-align: right;;">2023541</td><td style=";">10013024 Cardinal Essentials 6x6</td><td style="text-align: right;;">3000</td><td style=";">REP</td><td style="text-align: right;;">£475.00</td><td style=";">F1291G2</td><td style=";">RTE</td><td style="text-align: right;;">24</td><td style=";">386X576.25</td><td style=";">1X1</td><td style=";">K,CG6,7516,1505,185,LSGV</td><td style=";">FBB540423</td><td style="text-align: right;;">2428</td><td style="text-align: right;;">£1,425.00</td><td style=";">RACK A co all del 6/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Wed</td><td style=";">05.30pm</td><td style="text-align: right;;">30314</td><td style="text-align: right;;">2</td><td style=";">ADVMED</td><td style="text-align: right;;">2023541</td><td style=";">10013020 Cardinal Essentials 4x4</td><td style="text-align: right;;">3000</td><td style=";">REP</td><td style="text-align: right;;">£452.60</td><td style=";">F1173</td><td style=";">RTE</td><td style="text-align: right;;">19</td><td style=";">326.8X476.5</td><td style=";">1X1</td><td style=";">K,CG6,7516,1505,185,LSGV</td><td style=";">FBB540370</td><td style="text-align: right;;">2078</td><td style="text-align: right;;">£1,357.80</td><td style=";">RACK A co all del 6/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Wed</td><td style=";">07.30pm</td><td style="text-align: right;;">30296</td><td style="text-align: right;;">2</td><td style=";">NUTGRO</td><td style="text-align: right;;">59106</td><td style=";">CAR91607 Original Hair Support Vits 60s France</td><td style="text-align: right;;">5800</td><td style=";">NEW</td><td style="text-align: right;;">£164.85</td><td style=";">F1603</td><td style=";">RTE</td><td style="text-align: right;;">10</td><td style=";">243x220.25</td><td style=";">1x1</td><td style=";">1895,Green Opaque 318,Opaque Grey 425,SMV</td><td style=";">FBB450318</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">£956.13</td><td style=";">wait app co all del 28/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">Wed</td><td style=";">09.30pm</td><td style="text-align: right;;">30302</td><td style="text-align: right;;">2</td><td style=";">PROBIO</td><td style=";">P108183</td><td style=";">P10093-01 (PSB-021) Pro-Kolin+ 60ml TVM</td><td style="text-align: right;;">6000</td><td style=";">REP</td><td style="text-align: right;;">£211.76</td><td style=";">F1818</td><td style=";">OTE</td><td style="text-align: right;;">18</td><td style=";">363X216.25</td><td style=";">1X2</td><td style=";">K,C,M,Y,CG8,7459,7704,LSGV</td><td style=";">INV435413</td><td style="text-align: right;;">1952</td><td style="text-align: right;;">£1,270.56</td><td style=";">RACK A co all del 27/8 - ON HOLD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">Thur</td><td style=";">08.30am</td><td style="text-align: right;;">30299</td><td style="text-align: right;;">2</td><td style=";">PROBIO</td><td style=";">P108183</td><td style=";">P10019-02 (PSB-047) Synbiotec D-C 50 Cap TVM</td><td style="text-align: right;;">4000</td><td style=";">REP</td><td style="text-align: right;;">£299.99</td><td style=";">F2059</td><td style=";">OTE</td><td style="text-align: right;;">20</td><td style=";">212X236.25</td><td style=";">1X2</td><td style=";">K,C,M,Y,CG8,7459,7704,LSGV</td><td style=";">INV435305</td><td style="text-align: right;;">1543</td><td style="text-align: right;;">£1,199.96</td><td style=";">RACK A co all del 27/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">Thur</td><td style=";">10:30am</td><td style="text-align: right;;">30300</td><td style="text-align: right;;">2</td><td style=";">PROBIO</td><td style=";">P108183</td><td style=";">P10091-01(PSB-052) Pro-Kolin+ 15ml</td><td style="text-align: right;;">11000</td><td style=";">REP</td><td style="text-align: right;;">£104.00</td><td style=";">F2181</td><td style=";">OTE</td><td style="text-align: right;;">12</td><td style=";">271X136.25</td><td style=";">1X2</td><td style=";">K,C,M,Y,CG8,7459,7704,LSGV</td><td style=";">INV435305</td><td style="text-align: right;;">2303</td><td style="text-align: right;;">£1,144.00</td><td style=";">RACK A co all del 27/8 - ON HOLD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">Thur</td><td style=";">12.30pm</td><td style="text-align: right;;">30301</td><td style="text-align: right;;">2</td><td style=";">PROBIO</td><td style=";">P108183</td><td style=";">P10092-01(PSB-020) Pro-Kolin+ 30ml TVM</td><td style="text-align: right;;">11000</td><td style=";">REP</td><td style="text-align: right;;">£120.00</td><td style=";">F1802</td><td style=";">OTE</td><td style="text-align: right;;">15</td><td style=";">317X172.25</td><td style=";">1X2</td><td style=";">K,C,M,Y,CG8,7459,7704,LSGV</td><td style=";">INV435365</td><td style="text-align: right;;">2785</td><td style="text-align: right;;">£1,320.00</td><td style=";">RACK A co all del 27/8 - ON HOLD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">Thur</td><td style=";">02:30pm</td><td style="text-align: right;;">30072</td><td style="text-align: right;;">8</td><td style=";">LALKIR</td><td style=";">PO028366</td><td style=";">CT001045 Acatar Control 15ml</td><td style="text-align: right;;">187000</td><td style=";">REP</td><td style="text-align: right;;">£33.95</td><td style=";">F2006B1</td><td style=";">RTE</td><td style="text-align: right;;">15</td><td style=";">213X168.25</td><td style=";">2X2</td><td style=";">C,280,LSGV</td><td style=";">FBB400423</td><td style="text-align: right;;">19368</td><td style="text-align: right;;">£6,348.65</td><td style=";">RACK A co all del 9/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">Fri</td><td style=";">07.30am</td><td style="text-align: right;;">30276</td><td style="text-align: right;;">2</td><td style=";">LALKIR</td><td style=";">PO029262</td><td style=";">CT001044 Acatar Control 15ml</td><td style="text-align: right;;">16000</td><td style=";">NEW</td><td style="text-align: right;;">£88.71</td><td style=";">F2006B1</td><td style=";">RTE</td><td style="text-align: right;;">15</td><td style=";">213x168.25</td><td style=";">2x2</td><td style=";">C,280,LSGV</td><td style=";">FBB400423</td><td style="text-align: right;;">1886</td><td style="text-align: right;;">£1,419.36</td><td style=";">await app co all del 9/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style="text-align: right;;"></td><td style=";">PRINT & DIE CUT PLANNING SCHEDULE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Incarda Exel</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ENSOCOAT</td><td style=";">SILK</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mon - Fri - Double Days</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Performa Cream</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">INVERCOTE G</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style="text-align: right;;"></td><td style=";">G2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Arktika</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">KORSNAS</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style=";">Day</td><td style=";">Time</td><td style=";">Job no</td><td style=";">Hours</td><td style=";">Customer</td><td style=";">Cust Ord No</td><td style=";">Product Code / Title</td><td style=";">Quantity</td><td style=";">Rep/New</td><td style=";">Price/1000</td><td style=";">Cutter No</td><td style=";">Style</td><td style=";">Cyl Size</td><td style=";">Blank Size</td><td style=";">No Up</td><td style=";">Colours</td><td style=";">Board</td><td style=";">Meters</td><td style=";">OrdVal</td><td style=";">Status - Delivery Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style=";">Mon</td><td style=";">07.00am</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">MAINTENANCE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style=";">Mon</td><td style=";">12:00pm</td><td style="text-align: right;;">30322</td><td style="text-align: right;;">2</td><td style=";">PROBIO</td><td style=";">P108203</td><td style=";">P10025-05(PSB-005) Bio-Kult Infantis 16x1g</td><td style="text-align: right;;">7000</td><td style=";">REP</td><td style="text-align: right;;">£132.46</td><td style=";">F1805</td><td style=";">OTE</td><td style="text-align: right;;">10</td><td style=";">226X238.25</td><td style=";">1X1</td><td style=";">K,Neat Cyan,M,336,1585,LSGV</td><td style=";">INV435305</td><td style="text-align: right;;">2370</td><td style="text-align: right;;">£927.22</td><td style=";">RACK A co all del 29/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">40</td><td style=";">Mon</td><td style=";">02:00pm</td><td style="text-align: right;;">30323</td><td style="text-align: right;;">1.5</td><td style=";">PROBIO</td><td style=";">P108203</td><td style=";">P10206-01(PSB-005) Protexin Restore 16 PH1167-C</td><td style="text-align: right;;">2000</td><td style=";">REP</td><td style="text-align: right;;">£423.00</td><td style=";">F1805</td><td style=";">OTE</td><td style="text-align: right;;">10</td><td style=";">226X238.25</td><td style=";">1X1</td><td style=";">K,Protexin Pink,487,1585,3415,7606,LSGV</td><td style=";">INV435305</td><td style="text-align: right;;">934</td><td style="text-align: right;;">£846.00</td><td style=";">RACK A co all del 29/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">41</td><td style=";">Mon</td><td style=";">03:30pm</td><td style="text-align: right;;">30304</td><td style="text-align: right;;">3.5</td><td style=";">ADVMED</td><td style="text-align: right;;">2023540</td><td style=";">10013156 Trofolastin 5x7.5</td><td style="text-align: right;;">16000</td><td style=";">REP</td><td style="text-align: right;;">£104.01</td><td style=";">F1032</td><td style=";">RTE</td><td style="text-align: right;;">10</td><td style=";">234X246</td><td style=";">1X1</td><td style=";">K,8483,877,LSGV</td><td style=";">FBB540311</td><td style="text-align: right;;">4949</td><td style="text-align: right;;">£1,664.16</td><td style=";">RACK A co all del 27/8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">42</td><td style=";">Mon</td><td style=";">07.00pm</td><td style="text-align: right;;">30319</td><td style="text-align: right;;">2</td><td style=";">THOCAP</td><td style="text-align: right;;">140757</td><td style=";">LA631-1 Viterra Mulher Platinum 55+</td><td style="text-align: right;;">9600</td><td style=";">REP</td><td style="text-align: right;;">£180.00</td><td style=";">F1439E1</td><td style=";">RTE</td><td style="text-align: right;;">10</td><td style=";">192x200.25</td><td style=";">2x1</td><td style=";">OW,C,M,Y,326,Viterra Blue,OW,LSGV</td><td style=";">FOIL415413</td><td style="text-align: right;;">1637</td><td style="text-align: right;;">£1,728.00</td><td style=";">RACK A co all del 22/10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">43</td><td style=";">Mon</td><td style=";">09.30pm</td><td style="text-align: right;;">30295</td><td style="text-align: right;;">4</td><td style=";">HERCOS</td><td style=";">PO00049542</td><td style=";">SEC00445 Viviscal Densifying Oil Elixar GL</td><td style="text-align: right;;">17500</td><td style=";">REP</td><td style="text-align: right;;">£252.00</td><td style=";">F2176</td><td style=";">RTE Fitment</td><td style="text-align: right;;">13</td><td style=";">253.4x310.25</td><td style=";">1x1</td><td style=";">OW Tint Mix, Viviscal Red, OW, K, K, LSGV</td><td style=";">FOIL415360</td><td style="text-align: right;;">6970</td><td style="text-align: right;;">£4,410.00</td><td style=";">RACK A co all del 23/9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">£52,765.82</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br />
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,567
Office Version
365
Platform
Windows
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.
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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.
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,567
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,089,340
Messages
5,407,692
Members
403,158
Latest member
Limerick2030

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top