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?
 
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!
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)
 
Upvote 0
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
 
Upvote 0
Try
Code:
         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000)[COLOR=#ff0000] + 1[/COLOR]
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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