+ Reply to Thread
Results 1 to 4 of 4

Using CELLS function to define RANGE funcion

  1. #1
    Brian Barbre
    Guest

    Using CELLS function to define RANGE funcion

    I am writing this VB code

    Set MyCells = Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3000))

    I then want to use MyCells as a reference for a Application function.
    However, I get the "Application-defined or object-defined error" message.
    Anyone know what I might be doing wrong here?

    Thanks
    Brian


  2. #2
    Jim Thomlinson
    Guest

    RE: Using CELLS function to define RANGE funcion

    A couple of minor problems in there. Give this a try...

    Sub test()
    Dim MyCells As Range

    With Worksheets("Sheet2")
    Set MyCells = .Range(.Cells(1, 1), .Cells(3000, 1))
    End With
    MsgBox Application.Sum(MyCells)
    End Sub

    --
    HTH...

    Jim Thomlinson


    "Brian Barbre" wrote:

    > I am writing this VB code
    >
    > Set MyCells = Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3000))
    >
    > I then want to use MyCells as a reference for a Application function.
    > However, I get the "Application-defined or object-defined error" message.
    > Anyone know what I might be doing wrong here?
    >
    > Thanks
    > Brian
    >


  3. #3
    Kleev
    Guest

    RE: Using CELLS function to define RANGE funcion

    cellS(1,3000) is row 1 column 3000 but there are only 256 columns, thus the
    error.

    "Brian Barbre" wrote:

    > I am writing this VB code
    >
    > Set MyCells = Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3000))
    >
    > I then want to use MyCells as a reference for a Application function.
    > However, I get the "Application-defined or object-defined error" message.
    > Anyone know what I might be doing wrong here?
    >
    > Thanks
    > Brian
    >


  4. #4
    Brian Barbre
    Guest

    RE: Using CELLS function to define RANGE funcion

    Thanks Jim, that solved my problem.

    "Jim Thomlinson" wrote:

    > A couple of minor problems in there. Give this a try...
    >
    > Sub test()
    > Dim MyCells As Range
    >
    > With Worksheets("Sheet2")
    > Set MyCells = .Range(.Cells(1, 1), .Cells(3000, 1))
    > End With
    > MsgBox Application.Sum(MyCells)
    > End Sub
    >
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "Brian Barbre" wrote:
    >
    > > I am writing this VB code
    > >
    > > Set MyCells = Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3000))
    > >
    > > I then want to use MyCells as a reference for a Application function.
    > > However, I get the "Application-defined or object-defined error" message.
    > > Anyone know what I might be doing wrong here?
    > >
    > > Thanks
    > > Brian
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1