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

1. ## Re: Sum in selected cell

Also should the 2nd line of your data be 0.5 rather than 1.5?

2. ## 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. ## Re: Sum in selected cell

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

4. ## 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. ## Re: Sum in selected cell

Originally Posted by Fluff
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. ## 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)15000NEW£353.00F2341Unglued Wallet10161x2142x1N/A420413PE2172£5,295.00RACK A co all del 5/9 - appertures need removing
7Mon09:30am302921.5CATPHA101506 EO288262 3X BT (A11213)2000NEW£716.00F2338RTE with Fitment16321.7x390.251x1LSGVFBB5403701135£1,432.00RACK A co all del 6/9 - appertures need removing
8Mon11:00am302931CATPHA101507 EO288263 INNER CT BX (A11214)800NEW£1,024.00F2339CRASHLOCK25356.6x624.251x1LSGVFBB540423784£819.20RACK A co all del 6/9 - appertures need removing
9Mon12:00pm302911.5TORBAYPH36268Y4268JS/1 Amargine MS Carton1000REP£626.53F2143RTE14242x344.251x1K,C,396,LSGVFBB440318684£626.53RACK A co all del 22/08
10Mon01:30pm302942TORBAYPH362683416NS01 Morphine Sulfate 2mg in 1mL15000NEW£58.90F2082RTE17207x196.252x2K,7409,364,LSGVFBB4404232247£883.50RACK A co all del 22/08
11Mon03:30pm301362LALKIRPO028603CT001285 Man Cave Beard Oil 50ml17200REP£66.30F1155RTE13208X152.252X2K,2322,SMMV,FBGLFBB4504231908£1,140.36RACK A co all del 9/9
12Mon05.30pm303202THOCAP140757LA750-4 Vitace Vitamin C Carton20000REP£75.69F2150RTE19213x230.252x2K,M,Y,1665,349,LSGVFBB4504233246£1,513.80RACK A co all del 30/10
13Mon07.30pm303132CROVETPA330658DEC204A Dectospot 500ml Ireland4000REP£446.00F1069RTE14346x334.51x1K,C,M,Y,Dectospot Red 186,LSGVFBB5003931961£1,784.00RACK A co all del 3/9
14Tue07.00am302422.5PROBIOP108112P10212-02(PSB-005) Restore Vortexin 16x1g10000NEW£105.00F1591OTE20226x238.251x2K,Restore Orange,M,487,3415,UVSILK4853053246£1,050.00RACK A co all del 23/8
15Tue09:30am302742.5AMIMEDPO 2308S0295-3v Maxalt Melt 10mg20000AMD£71.67F1651B1RTE11182X252.252X1K,301,LSGVFBB4003653463£1,433.40RACK A co all del 28/8
16Tue12:00pm303102ADVMED202354110011283 Activheal HCD Foam 5x7.54000REP£241.08F1035RTE12244X276.251X1K,151,LSGVFBB5403111652£964.32RACK A co all del 4/9
17Tue02:00pm303212ADVMED202356810014575 Melgisorb USA Silver Alg 5x55000REP£197.71F1035RTE12244X276.251X1361 Tone,CG10,361 Line,LSGVFBB5403112028£988.55RACK A co all del 6/9
18Tue04:00pm303266ADVMED202357510009260 Maxorb Extra Alg 4x429000REP£126.49F1036RTE16270X376.251x1KTB,C,M,Y,MDG362,MLG359,MB,LSGVFBB54031113928£3,668.21RACK A co all del 29/08
19Wed07.00am303271.5ADVMED202357510010754 Maxorb Extra Ag 6x63000REP£521.41F1109RTE18279x4451x1KTB,C,M,Y,MDG362,877,MB,LSGVFBB5403111952£1,564.23RACK A co all del 29/08
20Wed08.30am303162ADVMED202354110013026 Cardinal Calcium Alg 4 x 4.753000REP£390.20F1020RTE16283X3961X1K Tone,K Line,2725 Opaque,185,LSGVFBB5403111702£1,170.60RACK A co all del 6/9
21Wed10:30am303125ADVMED202354110012160 Silvercel 10x20 Europe19000REP£127.12F1071RTE15370X3511X1K,CG9 10%,219,CG9,SVFBB5404238625£2,415.28RACK A co all del 4/9
22Wed03:30pm303152ADVMED202354110013024 Cardinal Essentials 6x63000REP£475.00F1291G2RTE24386X576.251X1K,CG6,7516,1505,185,LSGVFBB5404232428£1,425.00RACK A co all del 6/9
23Wed05.30pm303142ADVMED202354110013020 Cardinal Essentials 4x43000REP£452.60F1173RTE19326.8X476.51X1K,CG6,7516,1505,185,LSGVFBB5403702078£1,357.80RACK A co all del 6/9
24Wed07.30pm302962NUTGRO59106CAR91607 Original Hair Support Vits 60s France5800NEW£164.85F1603RTE10243x220.251x11895,Green Opaque 318,Opaque Grey 425,SMVFBB4503181800£956.13wait app co all del 28/8
25Wed09.30pm303022PROBIOP108183P10093-01 (PSB-021) Pro-Kolin+ 60ml TVM6000REP£211.76F1818OTE18363X216.251X2K,C,M,Y,CG8,7459,7704,LSGVINV4354131952£1,270.56RACK A co all del 27/8 - ON HOLD
26Thur08.30am302992PROBIOP108183P10019-02 (PSB-047) Synbiotec D-C 50 Cap TVM4000REP£299.99F2059OTE20212X236.251X2K,C,M,Y,CG8,7459,7704,LSGVINV4353051543£1,199.96RACK A co all del 27/8
27Thur10:30am303002PROBIOP108183P10091-01(PSB-052) Pro-Kolin+ 15ml11000REP£104.00F2181OTE12271X136.251X2K,C,M,Y,CG8,7459,7704,LSGVINV4353052303£1,144.00RACK A co all del 27/8 - ON HOLD
28Thur12.30pm303012PROBIOP108183P10092-01(PSB-020) Pro-Kolin+ 30ml TVM11000REP£120.00F1802OTE15317X172.251X2K,C,M,Y,CG8,7459,7704,LSGVINV4353652785£1,320.00RACK A co all del 27/8 - ON HOLD
29Thur02:30pm300728LALKIRPO028366CT001045 Acatar Control 15ml187000REP£33.95F2006B1RTE15213X168.252X2C,280,LSGVFBB40042319368£6,348.65RACK A co all del 9/9
30Fri07.30am302762LALKIRPO029262CT001044 Acatar Control 15ml16000NEW£88.71F2006B1RTE15213x168.252x2C,280,LSGVFBB4004231886£1,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 16x1g7000REP£132.46F1805OTE10226X238.251X1K,Neat Cyan,M,336,1585,LSGVINV4353052370£927.22RACK A co all del 29/8
40Mon02:00pm303231.5PROBIOP108203P10206-01(PSB-005) Protexin Restore 16 PH1167-C2000REP£423.00F1805OTE10226X238.251X1K,Protexin Pink,487,1585,3415,7606,LSGVINV435305934£846.00RACK A co all del 29/8
41Mon03:30pm303043.5ADVMED202354010013156 Trofolastin 5x7.516000REP£104.01F1032RTE10234X2461X1K,8483,877,LSGVFBB5403114949£1,664.16RACK A co all del 27/8
42Mon07.00pm303192THOCAP140757LA631-1 Viterra Mulher Platinum 55+9600REP£180.00F1439E1RTE10192x200.252x1OW,C,M,Y,326,Viterra Blue,OW,LSGVFOIL4154131637£1,728.00RACK A co all del 22/10
43Mon09.30pm302954HERCOSPO00049542SEC00445 Viviscal Densifying Oil Elixar GL17500REP£252.00F2176RTE Fitment13253.4x310.251x1OW Tint Mix, Viviscal Red, OW, K, K, LSGVFOIL4153606970£4,410.00RACK A co all del 23/9
44£52,765.82

Data

7. ## 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.

8. ## 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. ## 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. ## Re: Sum in selected cell

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```