VB.Net - Excel Sum Column Values Referencing Column With A Variable

Posted on 2014-08-27
Medium Priority
Last Modified: 2014-08-31
Good Day Experts!

I am having a bit of trouble here trying to figure out my SUM formula using variable for the column.  I can figure it out with a variable for the row end.

This works if I hard code the Column:

oSheet.Cells(DataRow + 2, ColumnStart).Formula = "=SUM(C7:C" & (DataRow) & ")"

Is it possible to use variable on Column reference?

Question by:Jimbo99999
  • 2
  • 2
LVL 15

Accepted Solution

ChloesDad earned 2000 total points
ID: 40289211
If the column variable is a string then you can do it this way

oSheet.Cells(DataRow + 2, ColumnStart).Formula = "=SUM(" & column & "7:" & Column & DataRow & ")"

Or if its a number

    Column = 4
    oSheet.Cells(DataRow + 2, ColumnStart).Formula .Formula = "=SUM(" & Range(Cells(1, Column), Cells(7, Column)).Address(False, False) & ")"

Author Comment

ID: 40289318
Thanks CholesDad...I will try it now!
LVL 15

Expert Comment

ID: 40289681
I have just noticed that I have an extra .Formula in the number part. This should be removed.

Author Comment

ID: 40295943
It is working now!

Thanks for helping.  

I have several other Excel formatting things I will need to be doing, but this is good to get it out of the way.

Thanks again,

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question