Sum in selected cell

leaper1981

New Member
Joined
Aug 16, 2019
Messages
26
Hi,

Just wondering if anyone could help. I want to create a Macro that does the following:

I want to select any cell I have a figure in (say A1 has the number 3 in) and when I run the macro it does a sum related to that number, ie: number in cell (3 in this example) +10%, divided by 2, + .5 = (and the cell now shows the result.

So basically I want it to do a sum related to the value in any given cell. I can work out how to do the sum but I dont know how you tell a Macro to do that in whichever cell you currently have selected.

Can anyone help please?
 
Also should the 2nd line of your data be 0.5 rather than 1.5?
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.


Book1
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
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top