[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Excel - VBA (Calculating Columns)

Hi,
In this section of the code, where the columns are being Sum, Expert Help have some columns identify with a semicolon and some identify with a comma.  What is the difference?   I bolded two examples below.
I have to update the codes to calculate on more columns but did not know if I should use a coma or a semi colon.

ws.Range("D" & DestRow) = "Complex Totals"
        ws.Range("G" & DestRow).Formula = "=SUM(G6:G" & DestRow - 1 & ")"
        ws.Range("G" & DestRow).Copy
        ws.Range("T" & DestRow & ":V" & DestRow & ",P" & DestRow & ":R" & DestRow & ",M" & DestRow & _
        ":N" & DestRow & ",J" & DestRow & ":K" & DestRow & ",H" & DestRow).PasteSpecial Paste:=xlPasteFormulas, _
0
ArisaAnsar
Asked:
ArisaAnsar
  • 5
  • 3
1 Solution
 
SiddharthRoutCommented:
Since You already asked the 2nd question after the 1st question was posted and closed here

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26963404.html?cid=748#a35690434

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

Sid
0
 
ArisaAnsarAuthor Commented:

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
0
 
SiddharthRoutCommented:
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

Open in new window


Now Try this.

Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
>>>You can use V14:ZZ14

Oops a type. I meant

You can use V14:AA14

Sid
0
 
SiddharthRoutCommented:
I found one more error in your main code

Replace

ws.Range("I" & DestRow & ",L" & DestRow & ",O" & DestRow & ",R" & DestRow & _
        ",U" & DestRow & ",AB" & DestRow & ",AH" & DestRow ",AI" & DestRow).NumberFormat = "0.000%"

With

        ws.Range("I" & DestRow & ",L" & DestRow & ",O" & DestRow & ",R" & DestRow & _
        ",U" & DestRow & ",AB" & DestRow & ",AH" & DestRow & ",AI" & DestRow).NumberFormat = "0.000%"

Open in new window


Sid
0
 
ArisaAnsarAuthor Commented:
This worked great.  Thank you very much.
0
 
ArisaAnsarAuthor Commented:
Thank you as always.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now