# Sum in selected cell

#### Fluff

##### MrExcel MVP, Moderator
Also should the 2nd line of your data be 0.5 rather than 1.5?

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

Last edited by a moderator:

#### Fluff

##### MrExcel MVP, Moderator
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
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
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
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``````

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