+ Reply to Thread
Results 1 to 8 of 8

Help to Shorten Code

Hybrid View

  1. #1
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Help to Shorten Code

    Can anyone assist in shortening the below code
    Dim LR As Long
    LR = Range("E" & Rows.Count).End(xlUp).Row
    Range("F" & LR + 1).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("F2:F" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 1).End(xlUp).Row
    Range("G" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("G2:G" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 2).End(xlUp).Row
    Range("H" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("H2:H" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 3).End(xlUp).Row
    Range("I" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("I2:I" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 4).End(xlUp).Row
    Range("J" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("J2:J" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 5).End(xlUp).Row
    Range("K" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("K2:K" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 6).End(xlUp).Row
    Range("L" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("L2:L" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 7).End(xlUp).Row
    Range("M" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("M2:M" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 8).End(xlUp).Row
    Range("N" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("N2:N" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 9).End(xlUp).Row
    Range("P" & LR + 0).Value = WorksheetFunction.SumIf(Range("E2:E" & LR), "PC STRAND - 1/2", Range("P2:P" & LR))
    LR = Range("E" & Rows.Count).Offset(0, 1).End(xlUp).Row
    Range("P" & LR + 0).Value = WorksheetFunction.Sum(Range("F" & LR + 0 & ":G" & LR + 0 & ":H" & LR + 0 & ":I" _
    & LR + 0 & ":J" & LR + 0 & ":K" & LR + 0 & ":L" & LR + 0 & ":M" & LR + 0 & ":N" & LR + 0 & ":P" & LR + 0))

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Help to Shorten Code

    What I have been doing recently is hiding the formulas on a reference sheet, then just setting the range.formula = source.formula. This also allows someone to make a simple formula change without knowing anything about VBA.

    I strongly dislike housing the formula IN vba because dealing with double quotes is proper annoying. That might be an idea for you.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Help to Shorten Code

    Thanks mikeTron, but I really need to keep the formulas in vba. There are many other things involved that is making it a necessary evil.

  4. #4
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Help to Shorten Code

    Any Suggestions I'm really struggling with this code
    Last edited by guitarsweety; 11-22-2014 at 02:59 PM.

  5. #5
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Help to Shorten Code

    Any Suggestions I'm really struggling with this code

  6. #6
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Help to Shorten Code

    152 + post, still you are expecting result of macro without sample file and describing requirement..

    delete those code.. and let us know.. whst is expected output, with sample file..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  7. #7
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Help to Shorten Code

    How rude! I will NEVER post in this forum again.

  8. #8
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Help to Shorten Code

    I apologize for being rude.. we will be happy if you spend some time here..
    http://www.excelforum.com/tips-and-t...d-answers.html

    We are still here.. to welcome you again..

    BTW.. we have another sub-forum.. where you can show your rudeness.. we will accept it happily..
    http://www.excelforum.com/commercial-services/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Very inefficient code because of different sizes of arrays, how do i shorten my code?
    By Brammer88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-28-2012, 04:49 PM
  2. How can I shorten this VBA code?
    By hemi_fan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2010, 11:53 AM
  3. How to shorten the code?
    By joyce_88 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-29-2008, 06:37 PM
  4. Shorten Code?
    By Mhz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2007, 06:18 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1