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

Posted on 2014-08-27
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
    LVL 15

    Accepted Solution

    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

    Thanks CholesDad...I will try it now!
    LVL 15

    Expert Comment

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

    Author Comment

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now