Creating a formula to put in a spreadsheet created with

Posted on 2006-03-20
Last Modified: 2010-04-23

Okay, I have the following For loop, and I need to calculate the following value, but I don't have a clue...

What I want is:

K4 = ( H4 - ( I4 + J4 ) )
K5 = ( H5 - ( I5 + J5 ) )
K6 = ( H6 - ( I6 + J6 ) )
K7 = ( H7 - ( I7 + J7 ) )
K26 = ( H26 - ( I26 + J26 ) )


Number of Cars = 23
I Column has an integer value of 8
H Column has an integer value of 9
J Column has an integer value of 10
K Column has an integer value of 11

Now here is the for loop

        CarNumberPositionCounter = 0
        For CheckAmountCounter As Integer = 1 To NumberOfCars

            CellRow = 3 + CheckAmountCounter
            Dim CellColumn2 As Integer = 11

            oSheet.Cells(CellRow, CellColumn2).Value = (oSheet.Cells(CellRow, 8) - (oSheet.Cells(CellRow, 9) + oSheet.Cells(CellRow, 10))) 'This doesn't work, but you get the idea
            CheckAmountCounter += 1

Question by:indy500fan
    LVL 10

    Assisted Solution

    did u try it by including the value property

    oSheet.Cells(CellRow, 8).Value - (oSheet.Cells(CellRow,9).Value + oSheet.Cells(CellRow, 10).Value)

    LVL 96

    Accepted Solution

    Wouldn't it be just as effective to let Excel do that, with a formula in a cell?


    Author Comment


    I think this is what you are talking about...

            oRng = oSheet.Range("K4", "K" & NumberOfCars + 3)
            oRng.Formula = "=H4 - (I4+J4)"

    This is what I used to make it work...

    bchoor, I didn't try it because I figured out the solution above, but...If you think it'll work, I'll give you the points.


    Author Comment

    Since you guys responded, but I haven't heard back I'll split the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 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

    14 Experts available now in Live!

    Get 1:1 Help Now