Results 1 to 3 of 3

Can this code be shortened?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Can this code be shortened?

    I've got this really long macro that does exactly what I want, but a large chunk of the code at the end seems repetitive to me. But it was the only way I could figure to do what I need. I'm just wondering if some of it could be consolidated.
    I'll attach my practice workbook with a sheet before the macro runs at all, a second sheet where I've only run the macro to just before the repetitive part and a third sheet showing after the whole macro runs.
    Basically, what I have it doing is adding a total at the end of each section in "Account Activity Details", then adding a formula in H & I of the "Grand Total" row to sum up all of the section totals. Then, just below that, it adds a formula in I to sum up those totals. I had to designate the start of each section, then refer to the cells before or after them to tell it where to put the section totals. I can't refer to specific row numbers, because, naturally, the reports are different lengths each month.
    If this is already a good way to do it, then I'm fine with that, but if there's something more efficient, that'd be good too.
    Below is that part of the code:
    Set FCS = Range("B:B").Find("*First Cost*")
    Set INS = Range("B:B").Find("*Invoices*")
    Set FBS = Range("B:B").Find("*Freight*")
    Set LS = Range("B:B").Find("*Load")
    Set ARS = Range("B:B").Find("*Account Receipts")
    Set CS = Range("B:B").Find("*Claims")
    Set ERS = Range("B:B").Find("*Exchange*")
    Set LCS = Range("B:B").Find("*True Landed*")
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Set SL = Range("F:F").Find("Grand Total")
    
    With Range("H" & INS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & FCS.Row + 4 & ":H" & INS.Row - 3 & ")"
    End With
    With Range("I" & INS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & FCS.Row + 4 & ":I" & INS.Row - 3 & ")"
    End With
    With Range("H" & FBS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & INS.Row + 4 & ":H" & FBS.Row - 3 & ")"
    End With
    With Range("I" & FBS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & INS.Row + 4 & ":I" & FBS.Row - 3 & ")"
    End With
    With Range("H" & LS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & FBS.Row + 4 & ":H" & LS.Row - 3 & ")"
    End With
    With Range("I" & LS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & FBS.Row + 4 & ":I" & LS.Row - 3 & ")"
    End With
    With Range("J" & LS.Row - 2)
        .Formula = "=SUBTOTAL(9,J" & FBS.Row + 4 & ":J" & LS.Row - 3 & ")"
    End With
    With Range("K" & LS.Row - 2)
        .Formula = "=SUBTOTAL(9,K" & FBS.Row + 4 & ":K" & LS.Row - 3 & ")"
    End With
    With Range("H" & ARS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & LS.Row + 4 & ":H" & ARS.Row - 3 & ")"
    End With
    With Range("I" & ARS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & LS.Row + 4 & ":I" & ARS.Row - 3 & ")"
    End With
    With Range("H" & CS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & ARS.Row + 4 & ":H" & CS.Row - 3 & ")"
    End With
    With Range("I" & CS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & ARS.Row + 4 & ":I" & CS.Row - 3 & ")"
    End With
    With Range("H" & ERS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & CS.Row + 4 & ":H" & ERS.Row - 3 & ")"
    End With
    With Range("I" & ERS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & CS.Row + 4 & ":I" & ERS.Row - 3 & ")"
    End With
    With Range("H" & LCS.Row - 2)
        .Formula = "=SUBTOTAL(9,H" & ERS.Row + 4 & ":H" & LCS.Row - 3 & ")"
    End With
    With Range("I" & LCS.Row - 2)
        .Formula = "=SUBTOTAL(9,I" & ERS.Row + 4 & ":I" & LCS.Row - 3 & ")"
    End With
    With Range("H" & LR + 2)
        .Formula = "=SUBTOTAL(9,H" & LCS.Row + 4 & ":H" & LR & ")"
    End With
    With Range("I" & LR + 2)
        .Formula = "=SUBTOTAL(9,I" & LCS.Row + 4 & ":I" & LR & ")"
    End With
            
    With Range("H" & SL.Row)
        .Formula = "=SUBTOTAL(9,H" & FCS.Row & ":H" & LR & ") +" & "J" & LS.Row - 2
    End With
    With Range("I" & SL.Row)
        .Formula = "=SUBTOTAL(9,I" & FCS.Row & ":I" & LR & ") +" & "K" & LS.Row - 2
    End With
    
    With Range("I" & SL.Row + 1)
        .FormulaR1C1 = "=Sum(R[-1]C[-1]:R[-1]C)"
    End With
    Thanks!

    Jenny
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can this formula be shortened ?
    By Fantaxia in forum Excel General
    Replies: 12
    Last Post: 04-05-2014, 06:21 AM
  2. Can this formula be shortened?
    By zookeepertx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-04-2014, 02:54 PM
  3. [SOLVED] Shortened IF Formula
    By DeamonX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 06:21 AM
  4. Can this formula be shortened?
    By nandomer in forum Excel General
    Replies: 3
    Last Post: 12-01-2011, 05:05 PM
  5. Can this Formula be shortened
    By IsoTech in forum Excel General
    Replies: 6
    Last Post: 08-04-2010, 04:04 AM

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