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

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

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

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