Excel - VBA (Calculating Columns)

Since You already asked the 2nd question after the 1st question was posted and closed here

https://www.experts-exchange.com/questions/26963404/Excel-VBA-Formula-Updated.html?cid=748&anchorAnswerId=35690434#a35690434

It no point for me to post the answer again :)

Sid

Thanks again.  Did I get it correct?  The excel file has the columns with the formula and I pasted the codes below.


Option Explicit

Const MyFolder As String = "T:\Jennifer\Beyond Now - CSAs Beyond NOW\Connect Reports\Complex Reports\"

Sub Sample()
    Dim wb As Workbook, ws As Worksheet
    Dim LastRow As Long, DestRow As Long
    Dim StrFile As String
    Dim msgFooter As String
   
    '~~Footer Message
    msgFooter = "Account detail reports were posted to BOM and Service Reports Portal in Specials section." & Chr(10) & _
            "Reports are called:" & Chr(10) & _
            "Connect Edelivery-New Accts Enrolled April 16-29" & Chr(10) & _
            "Connect SAC-New Accts With SAC April 16-29" & Chr(10) & _
            "Connect FreeForever IRA-New Accts Enrolled April 16-29"

     
   
    StrFile = Dir$(MyFolder & "*.xls")
   
    Do While Len(StrFile)
        Set wb = Workbooks.Open(MyFolder & StrFile)
        Set ws = Sheets("DESTINATION")
       
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        DestRow = LastRow + 3
       
        ws.Range("D" & DestRow) = "Complex Totals"
        ws.Range("G" & DestRow).Formula = "=SUM(G6:G" & DestRow - 1 & ")"
        ws.Range("G" & DestRow).Copy
        ws.Range("H" & DestRow & ",J" & DestRow & ":K" & DestRow & ",M" & DestRow & ":N" & DestRow & _
       ",P" & DestRow & ":Q" & DestRow & ",S" & DestRow & ":T" & DestRow & ",V" & DestRow & _
       ":W" & DestRow & ",X" & DestRow & ":Y" & DestRow & DestRow & ":Z" & DestRow & ":AA").PasteSpecial Paste:=xlPasteFormulas, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       
        ws.Range("I" & DestRow).Formula = "=H" & DestRow & "/G" & DestRow
        ws.Range("L" & DestRow).Formula = "=(K" & DestRow & "/J" & DestRow & ")-$I$" & DestRow
        ws.Range("O" & DestRow).Formula = "=(N" & DestRow & "/M" & DestRow & ")-$I$" & DestRow
        ws.Range("R" & DestRow).Formula = "=(Q" & DestRow & "/P" & DestRow & ")-$I$" & DestRow
        ws.Range("U" & DestRow).Formula = "=(T" & DestRow & "/S" & DestRow & ")-$I$" & DestRow
     'Free Forever
        ws.Range("AB" & DestRow).Formula = "=(X" & DestRow & "+Y" & DestRow & "+Z" & DestRow & "+AA" & DestRow & ")/W" & DestRow
     'EDelivery
        ws.Range("AH" & DestRow).Formula = "=(AD" & DestRow & "+AE" & DestRow & "+AF" & DestRow & "+AG" & DestRow & ")/AG" & DestRow
    'Aggregate Increase
        ws.Range("AI" & DestRow).Formula = "=U" & DestRow & "+AB" & DestRow & "+AH" & DestRow

        ws.Rows(DestRow).NumberFormat = "0"
        ws.Range("I" & DestRow & ",L" & DestRow & ",O" & DestRow & ",R" & DestRow & _
        ",U" & DestRow & ",AB" & DestRow & ",AH" & DestRow ",AI" & DestRow).NumberFormat = "0.000%"
       
        '~~> Add the relevent Text to the Footer
        With ws.PageSetup
            .LeftFooter = msgFooter
        End With
       
   
        Application.StatusBar = " File " & wb.Name & " is being processed..."
        wb.Close savechanges:=True
        StrFile = Dir
    Loop
   
    MsgBox "Done"
   
    Set ws = Nothing
    Set wb = Nothing
End Sub


31-WEST-52ND-ST.xls

Change

ws.Range("H" & DestRow & ",J" & DestRow & ":K" & DestRow & ",M" & DestRow & ":N" & DestRow & _
       ",P" & DestRow & ":Q" & DestRow & ",S" & DestRow & ":T" & DestRow & ",V" & DestRow & _
       ":W" & DestRow & ",X" & DestRow & ":Y" & DestRow & DestRow & ":Z" & DestRow & ":AA").PasteSpecial Paste:=xlPasteFormulas, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

To

       

 ws.Range("H" & DestRow & ",J" & DestRow & ":K" & DestRow & ",M" & DestRow & ":N" & DestRow & _
        ",P" & DestRow & ":Q" & DestRow & ",S" & DestRow & ":T" & DestRow & ",V" & DestRow & _
        ":AA" & DestRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, _
        Transpose:=False

Select allOpen in new window

Now Try this.

Sid

Like I mentioned in the earlier thread, since the cells are one after the other

V,W,X,Y,Z,AA

You can use V14:ZZ14

Sid

>>>You can use V14:ZZ14

Oops a type. I meant

You can use V14:AA14

Sid

This worked great.  Thank you very much.

Thank you as always.