Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

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

  1. #21
    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

    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!

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

    Default 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)
    - 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. #23
    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

    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. #24
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    Running Office 365 on Win 10

  5. #25
    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

    That's perfect!

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

    Dan

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

    Default Re: Sum in selected cell

    You're welcome & thanks for the feedback
    - 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. #27
    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

    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. #28
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default 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
    - 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. #29
    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

    You've done it again! Thanks thats perfect.

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

    Default Re: Sum in selected cell

    Glad to help & thanks for the feedback
    - 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
  •