+ Reply to Thread
Results 1 to 10 of 10

VBA Macro to sum a variable range and put the result in the specified column

  1. 05-16-2016, 06:35 PM #1
    Wither125
    Wither125 is offline
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    VBA Macro to sum a variable range and put the result in the specified column

    Hello,

    I am creating a macro that would calculate the sum from the column(TOTAL_DLR_AM) of the table(calculation) and paste the calculated sum in the next table(Approvals Summary) in the column(Sum).
    However i try but i am only able to calculate the sum but not able to paste the calculated sum in the column(Sum).

    I am not using the range select because the data in the table (calculation) will very.

    Below is the code:

    Sub Macro()

    Windows("techdata.xlsm").Activate
    Sheet1.Activate
    ActiveCell.FormulaR1C1 = "Sum"
    ActiveCell.Offset(0, 1).Select
    myrow = Range("E6666").End(xlUp).Row
    ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

    End Sub

    I am not getting any errors when running the macro but it doesn't paste the calculated sum in the column (Sum).

    Looking forward for your answer and Thanks for your help in advance!
    Reply With QuoteRegister To Reply
  2. 05-16-2016, 07:30 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: VBA Macro to sum a variable range and put the result in the specified column

    I guess where it gets pasted will depend on which cell is active (selected) when the code is run. There seems to be an assumption there ...
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-17-2016, 05:00 PM #3
    Wither125
    Wither125 is offline
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA Macro to sum a variable range and put the result in the specified column

    i am summing the column (TOTAL_DLR_AM) and its working fine, but i am not able to put the calculated sum in the column named sum

    i would like to know how do i put the calculated sum in the column named sum.

    tech.png
    Reply With QuoteRegister To Reply
  4. 05-17-2016, 05:13 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: VBA Macro to sum a variable range and put the result in the specified column

    Suggest you post a sample workbook, not a picture. Include the formula(e) you are using and any existing code.
    Reply With QuoteRegister To Reply
  5. 05-17-2016, 05:30 PM #5
    Wither125
    Wither125 is offline
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA Macro to sum a variable range and put the result in the specified column

    i have attached the workbook and the code i am using is this:

    Sub Macro()

    Windows("tech.xlsx").Activate
    Sheet1.Activate
    ActiveCell.FormulaR1C1 = "Sum"
    ActiveCell.Offset(0, 1).Select
    myrow = Range("E6666").End(xlUp).Row
    ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"

    Sheet1.Columns().AutoFit

    End Sub

    The below codes i am using for calculating the sum for the column (TOTAL_DLR_AM) and its working , but how do i have put the calculated sum in the column named sum.

    myrow = Range("E6666").End(xlUp).Row
    ActiveCell.Offset(0, 4).Formula = "=SUM(E3:E" & myrow & ")"
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-17-2016, 05:59 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: VBA Macro to sum a variable range and put the result in the specified column

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-18-2016, 04:10 AM #7
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: VBA Macro to sum a variable range and put the result in the specified column

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    http://stackoverflow.com/questions/3...ecified-column
    http://www.mrexcel.com/forum/excel-q...ed-column.html
    http://answers.microsoft.com/en-us/o...55fb50/?auth=1
    http://software.moftalk.com/vba-macr...5VtDSqWwj.html
    http://www.excelguru.ca/forums/showt...ecified-column
    http://www.excelguru.ca/forums/showt...variable-range
    http://www.excelkey.com/forum/viewtopic.php?f=3&t=8363

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  8. 05-18-2016, 04:38 AM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: VBA Macro to sum a variable range and put the result in the specified column

    @Pepe: oh good grief ...
    Reply With QuoteRegister To Reply
  9. 05-18-2016, 05:24 PM #9
    Wither125
    Wither125 is offline
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA Macro to sum a variable range and put the result in the specified column

    Hello TMS,

    Thanks a lot ! your code has solved my problem.
    Reply With QuoteRegister To Reply
  10. 05-18-2016, 05:46 PM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,422

    Re: VBA Macro to sum a variable range and put the result in the specified column

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Possible adapt macro to write sum formula (variable sheets) instead of result?
    By Kamolga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2015, 10:28 AM
  2. [SOLVED] Can't LOOKUP a value in a range and get a result from column 1 of the range
    By bobboy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-26-2013, 09:06 PM
  3. [SOLVED] Help searching column for variable text and selecting result as range
    By jmetz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2012, 08:51 AM
  4. Macro to Sum Multiple Times in Column Based on Variable Range
    By kareni in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-22-2011, 08:15 PM
  5. Macro to Find any empty cell within a range and show result in another column
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2008, 11:32 AM
  6. 2 column variable, sum result
    By darkwood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2007, 07:45 PM
  7. Call VBScript from VBA, pass variable, return result to macro?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 07-11-2006, 02:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1