Total Excel columns from VB

I have written a VB program that creates an EXCEL spreadsheet.  I need to SUM columns E and F and G, then total those totals.  How do I do that?

I am able to get the last row used by doing:
        Range(Selection, Selection.End(xlDown)).Select
        varRow = Mid(Selection.End(xlDown).Address, 4)

Since I am working with test data and I know how many rows I have, the following formula works:
        ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)"

Obviously, when I use LIVE data, I won’t know what the last row will be, so I want to use my variable in the formula:
       ActiveCell.FormulaR1C1 = "=SUM(R[varRow]C:R[-1]C)"

but I get this error:
Run-time error ‘1004’:
Application-defined or object-defined error

Am I close?  What do I need to do?

Thanks for your help
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

bruintjeConnect With a Mentor Commented:
Hi Jhartski,

Richie mentioned something interesting there how are you using the objects have you named them all??

you said the Activecell formula works but not with a variable

here's a little snippet

Dim lngLastRow As Long
Dim lngFirstRow As Long
Dim lngColumn As Long

  lngColumn = Selection.Column
  lngFirstRow = Selection.Row
  lngLastRow = Selection.Row
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = WorksheetFunction.Sum(Range(Cells(lngFirstRow, lngColumn), Cells(lngLastRow, lngColumn)))

the Range("F6") is hypothetical since i don't know what your selection is to start with but this will sum from the selected cell to the last and add the total below it

Richie_SimonettiIT OperationsCommented:
Firts to all, Selection object works inside VBA workspaces not in VB (Anyway is a range object)
You must to strictly qualify that object so VB could know what you are talking about when you say Selection.
Richie_SimonettiIT OperationsCommented:
something like
dim rng as range
'sh points to a variable object of type worksheet
set rng = sh.range("E1:" & range("E1").end(xldown)
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Richie_SimonettiIT OperationsCommented:
dim LastCell as string
LastCell=range("E1:" & range("E1").end(xldown).address)
well thanks, glad it did work, then was this VBA or something
btw Richie congrats! top-15 now i want to c u go for the top-5 :)
Richie_SimonettiIT OperationsCommented:
Well, if you continue collaborating this way i doubt!
All Courses

From novice to tech pro — start learning today.