Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: Setting a range - sum a column - error 91

    I'm new to vba and object programming and am trying to take a column and simply sum it to use it as a test for whether or not to take action. I found a sample code on the net, but am getting error 91....not sure what I'm doing wrong. Can someone help?

    Thanks in advance...Pam

    Public rngMyCol As Range
    Sub CallIt()
    ActiveSheet.Columns(4).Select 'selecting a column on the spreadsheet
    'the following line errors (trying to set an object range to use in the next
    function - which is the one I found on the net)
    rngMyCol = Application.Cells.Columns
    Worksheet_SelectionChange (rngMyCol) ' call the function with the set range
    End Sub

    ' the following is the function that I "borrowed" from the net.....
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim myVar As Double
    myVar = Application.Sum(Columns(Target.Column))
    If myVar <> 0 Then
    Application.StatusBar = Format(myVar, "###,###")
    Else
    Application.StatusBar = False
    End If
    End Sub

  2. #2
    Join Date
    Dec 2004
    Posts
    37

    Gettingthe Total from a range

    The following works, although if the numbers in the range are not integers then the RngTotal would have to be 'Double' rather than 'Long'

    Sub SumRange()

    Dim Sht As Worksheet
    Dim Rng As Range
    Dim RngTotal As Long

    Set Sht = ThisWorkbook.Worksheets("Sheet1")
    Set Rng = Sht.Columns(2)
    RngTotal = Application.WorksheetFunction.Sum(Rng)

    End Sub

    Hope this helps
    Mike

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    Mike,
    Thanks - this was helpful. I can get this to work in a stand alone spreadsheet no problem...still working out getting it to work in my called procedure.
    Thanks again!
    Pam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •