adding cells

lcan4 · September 5th, 2006, 09:03 PM

I need for the code below to add all of the cells to row 69 in the D column after the bordered P2 row is found and place the results in cell O3. Can someone help me - Thanks in advance.

Public const Sales_Units_column = "03"

Public Sub SalesUntColumnAfterP2()
    Dim P5Row As Long
    'Get P5 Row
    For P5Row = Me.dataStartsIndex To engine.template.lastDataRow
        If cells(P2Row, SALES_UNITS_COLUMN).Borders(xlEdgeTop). _
            LineStyle = xlDouble Then
            Exit For
        End If

    End Sub

maccas · September 6th, 2006, 05:57 AM

Ican,

Its too hard tell exactly what you want the program to do. Could you restate the problem along the lines of what you would like the porgram to do. An example would be as follows:

* There's a series of numbers in column D of a spreadsheet between rows 1 and 69.
* I want to scan down the column until the top border of one of the cells is a double line.
*From this cell onwards (inclusive) I would like to add together the value of every cell.
* I would like the calculated sum to be pasted into cell O3 by way of result.

maccas · September 6th, 2006, 06:08 AM

By way of info, the following should (but not tested!) execute the instructions I listed out:

Code:

Sub CellTest()

Const intColumn As Integer = 4
Const intStartRow As Integer = 1
Const intEndRow As Integer = 69
Const strOutput As String = "O3"

Dim sh As Worksheet
Dim intRow As Integer
Dim Total As Double
Dim booSumming As Boolean

    ' Set up the worksheet we're interested in
    Set sh = ThisWorkbook.Sheets("Sheet1")

    ' Set starting values
    Total = 0
    booSumming = False

    ' Loop through every row
    For intRow = intStartRow To intmaxrow

        ' If we're summing then add to the total
        If booSumming Then

            If IsNumeric(sh.Cells(intRow, intColumn).Value) Then Total = Total + sh.Cells(intRow, intColumn).Value

        ' If we're not yet summing then check borders
        Else

            If Cells(intRow, intColumn).Borders(xlEdgeTop).LineStyle = xlDouble Then

                booSumming = True
                Total = sh.Cells(intRow, intColumn).Value

            End If

        End If

    Next intRow

    ' Output Result
    sh.Range(strOutput).Value = Total

End Sub