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?

 

[leaper1981]

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.

Thanks again for your Genius!

 

[Fluff]

It currently rounds up to the nearest 0.5, if you don't want that use.

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

 

[leaper1981]

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

 

[Fluff]

Try

         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000)[COLOR=#ff0000] + 1[/COLOR]

 

[leaper1981]

That's perfect!

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

Dan

 

[Fluff]

You're welcome & thanks for the feedback

 

[leaper1981]

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

 

[Fluff]

Change this

For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))

to

For Each Cl In Selection

But you need to select cells in Col A

 

[leaper1981]

You've done it again! Thanks thats perfect.

 

[Fluff]

Glad to help & thanks for the feedback