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

1. Re: Sum in selected cell

Hi again,

This is almost perfection!! Just one more thing (I know I said that before lol) Does it round up or down? eg: If the sum came out at 3.3, would it put 3.5 or would it put 3? I ask as usually we round it up not down.

Secondly if that could be problematic would it be possible for it to put the actual figure it comes out at (say 3.3465 or whatever) Then I could just look at all those figures and decide to round it up or down myself. It could be beneficial to see the exact figure I would have got on the calculator in the hours column just in case some jobs that are close to being the next number feel like they could do with adding an extra .5 on when I ***** the other factors we have for production.

2. Re: Sum in selected cell

It currently rounds up to the nearest 0.5, if you don't want that use.
Code:
`         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000)`

3. Re: Sum in selected cell

Hi me again,

Last one I promise! That looks great with it displaying the actual figure, would it be possible to add 1 onto the figures it is spitting out, I tried to alter the formula myself looking at your previous ones but its seemingly not as simple as just adding a 1 to the end (which is why you're the expert!) So if it currently comes out at say 3.8934......it adds an extra 1 to it and becomes 4.8934

After that I'll go away and leave you alone lol

Thanks again

4. Re: Sum in selected cell

Try
Code:
`         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000) + 1`

5. Re: Sum in selected cell

That's perfect!

I cant thank you enough for all your time and effort.

Dan

6. Re: Sum in selected cell

You're welcome & thanks for the feedback

7. Re: Sum in selected cell

Hi again,

You did this brilliant macro for me a few weeks ago & I was just wondering if it was possible to make a small change (maybe not)

Basically the macro below applies itself to all the cells column D, I was wondering if it would be possible for it to only apply to selected cells in row D rather than all. Thanks again, Dan.

Sub Hours()
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 = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000) + 1
End If
Next Cl
End Sub

8. Re: Sum in selected cell

Change this
Code:
`For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))`
to
Code:
`For Each Cl In Selection`
But you need to select cells in Col A

9. Re: Sum in selected cell

You've done it again! Thanks thats perfect.

10. Re: Sum in selected cell

Glad to help & thanks for the feedback