Sum in selected cell

[leaper1981]

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?

 

[Fluff]

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

 

[leaper1981]

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]

How about

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]

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]

How about

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]

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

 

[Fluff]

Try

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]

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]

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]

Ok, how about

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