Sum in selected cell

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,564
Office Version
365
Platform
Windows
Hi & welcome to MrExcel
How about
Code:
   With ActiveCell
      .Value = .Value * 1.1 / 2 + 0.5
   End With
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
206
Office Version
365, 2013
Platform
Windows
Put into sheet code:


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A1:A10]) Is Nothing Then        'set range as You need
    Target.Value = ((Target.Value * 1.1) / 2) + 0.5
End If
End Sub
 

Trixterz

New Member
Joined
Aug 15, 2019
Messages
11
Or this...

Code:
    Dim Results As String
    'Gets vaule from colume A.
    Results = Range("A" & ActiveCell.Row).Value
    Results = (Results + (Results * 0.1)) / 2 + 0.5
    
    'Post results in colume B.
    Range("B" & ActiveCell.Row).Value = Results
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
Or this...

Code:
    Dim Results As String
    'Gets vaule from colume A.
    Results = Range("A" & ActiveCell.Row).Value
    Results = (Results + (Results * 0.1)) / 2 + 0.5
    
    'Post results in colume B.
    Range("B" & ActiveCell.Row).Value = Results
Hi Guys,

Thanks for all your suggestions & sorry for my lack of understanding. This last one did exactly what I needed except it put the value in the cell next to it rather than over writing the value in the cell its working from (if that makes sense) I want it to replace the number.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,564
Office Version
365
Platform
Windows
This will put the result of the calculation in the active cell
Code:
Sub leaper1981()
   With ActiveCell
      .Value = .Value * 1.1 / 2 + 0.5
   End With
End Sub
 

Trixterz

New Member
Joined
Aug 15, 2019
Messages
11
Change the letter B to A should fixed the issue.

Code:
Dim Results As String
    'Gets value from column A.
    Results = Range("A" & ActiveCell.Row).Value
    Results = (Results + (Results * 0.1)) / 2 + 0.5
    
    'Override column A with the new results.
    Range("A" & ActiveCell.Row).Value = Results
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,564
Office Version
365
Platform
Windows
@Trixterz
That assumes the activecell is in col A, which may not always be the case.
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
DayTimeJob noHoursCustomerCust Ord NoProduct Code / TitleQuantity Rep/New Price/1000 Cutter NoStyleCyl SizeBlank SizeNo Up
Mon08.00am302981.5CATPHA102120 EO288459 10 CAV BC (A11297)15000NEW£353.00F2341Unglued Wallet10161x214 2x1
Mon09:30am302921.5CATPHA101506 EO288262 3X BT (A11213)2000NEW£716.00F2338 RTE with Fitment16321.7x390.25 1x1

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>


Scrap my previous request (sorry)

Just realised its harder than I thought due to the sum I need to do, didnt bother asking this as I thought it might be too difficult. Might make it easier to understand what I'm trying though.
The above is straight from the exel sheet i use. What I have to do is the following.
The hours are estimated originally & could be say 3 (as apposed to the 1.5 you can see as this has now been altered) How I end up at 1.5 is as follows
(order qty + 10%) / number up / 7000 = (1.178571428571429) I have then rounded it up to 1.5 (wouldn't require it to do that if not possible)

The problem I see here is the 'number up' Basically 2x1 would mean the figure I need to apply is 2, 1x1 would be 1, 2x2 would be 4 etc. Pretty sure Exel isnt going to be able to do that but possibly there is a way of getting it to ask you to enter the number up figure while it does the rest.

Not sure how easy this is to understand, I'm sure you guys would know what to do in a second if you were here lol If there is any chance of doing any of this but I havent given you enough info please let me know and I'll try to explain better.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,564
Office Version
365
Platform
Windows
Do you want to do this on the entire set, or just certain cells?
 

Forum statistics

Threads
1,089,334
Messages
5,407,649
Members
403,156
Latest member
Fahad Saleem

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top