Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 5th, 2006, 09:03 PM
Registered User
Join Date: Sep 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default adding cells

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

Old September 6th, 2006, 05:57 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


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.

Old September 6th, 2006, 06:08 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

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

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

            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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding cells from a datagrid mjbkelly VB Databases Basics 1 April 10th, 2007 12:28 PM
Datagrid and cells haroon .NET Framework 2.0 1 October 11th, 2004 10:46 AM
Cells doubleclick mushu Excel VBA 6 September 29th, 2004 02:52 AM
Protecting Cells jacks Excel VBA 1 February 20th, 2004 11:55 AM
Cells MattLeek Excel VBA 2 December 12th, 2003 11:54 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.