Page 1 of 3 123 LastLast
Results 1 to 10 of 30

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum in selected cell

    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?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Sum in selected cell

    Hi & welcome to MrExcel
    How about
    Code:
       With ActiveCell
          .Value = .Value * 1.1 / 2 + 0.5
       End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    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

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    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

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Quote Originally Posted by Trixterz View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Sum in selected cell

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Sum in selected cell

    @Trixterz
    That assumes the activecell is in col A, which may not always be the case.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum in selected cell

    Day Time Job no Hours Customer Cust Ord No Product Code / Title Quantity Rep/New Price/1000 Cutter No Style Cyl Size Blank Size No Up
    Mon 08.00am 30298 1.5 CATPHA 102120 EO 288459 10 CAV BC (A11297) 15000 NEW £353.00 F2341 Unglued Wallet 10 161x214 2x1
    Mon 09:30am 30292 1.5 CATPHA 101506 EO 288262 3X BT (A11213) 2000 NEW £716.00 F2338 RTE with Fitment 16 321.7x390.25 1x1


    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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Sum in selected cell

    Do you want to do this on the entire set, or just certain cells?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •