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

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

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
0
jhartski
Asked:
jhartski
  • 4
  • 3
1 Solution
 
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.
0
 
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)
0
 
bruintjeCommented:
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

  Range("F6").Select
  lngColumn = Selection.Column
  lngFirstRow = Selection.Row
  Selection.End(xlDown).Select
  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

:O)Bruintje
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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