Hi,

I am trying to read a single column into an array. The code that I
have written is giving me a Runtime Error '1004'- Application -
defined or Object - defined error. Could you please help me to
understand the mistake...The code is as below.

Function GenMArray() As Variant

ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Activate
ActiveCell.Offset(3, 1).Select

MsgBox "The address is " & ActiveCell.Address
GenMArray = Application.Transpose(Range(ActiveCell,
ActiveCell.End(xlDown)))

End Function

Thanks,
shi

Shi,

This worked for me:

Sub TryNow()
Dim myArray As Variant
Dim i As Integer

myArray = GenMArray
For i = LBound(myArray) To UBound(myArray)
MsgBox myArray(i)
Next i
End Sub

Function GenMArray() As Variant

ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Activate
ActiveCell.Offset(3, 1).Select

MsgBox "The address is " & ActiveCell.Address
GenMArray = Application.Transpose(Range(ActiveCell, ActiveCell.End(xlDown)))

End Function

HTH,
Bernie
MS Excel MVP


"shishi" wrote in message
oups.com...
Hi,

I am trying to read a single column into an array. The code that I
have written is giving me a Runtime Error '1004'- Application -
defined or Object - defined error. Could you please help me to
understand the mistake...The code is as below.

Function GenMArray() As Variant

ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Activate
ActiveCell.Offset(3, 1).Select

MsgBox "The address is " & ActiveCell.Address
GenMArray = Application.Transpose(Range(ActiveCell,
ActiveCell.End(xlDown)))

End Function

Thanks,
shi

Hi,

I have made my code exactly the same as the way Bernie has suggested,

but still it fails. I ran Debug-Step Into. When it executes the line
GenMArray = Application.Transpose(Range(Ac*tiveCell,
ActiveCell.End(xlDown))) ,
it come up with the Runtime Error '1004'- Application -
defined or Object - defined error. But if I place the mouse pointer
on top of the Range(Ac*tiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out
solutions to fix this problem..Thanks for all your advice.

shi

What does the second msgbox say:

Function GenMArray() As Variant

ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Activate
ActiveCell.Offset(3, 1).Select

MsgBox "The address is " & ActiveCell.Address
MsgBox "The array is " & Range(ActiveCell, ActiveCell.End(xlDown)).Address
GenMArray = Application.Transpose(Range(ActiveCell, ActiveCell.End(xlDown)))

End Function


HTH,
Bernie
MS Excel MVP


"shishi" wrote in message
oups.com...
Hi,

I have made my code exactly the same as the way Bernie has suggested,

but still it fails. I ran Debug-Step Into. When it executes the line
GenMArray = Application.Transpose(Range(Ac*tiveCell,
ActiveCell.End(xlDown))) ,
it come up with the Runtime Error '1004'- Application -
defined or Object - defined error. But if I place the mouse pointer
on top of the Range(Ac*tiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out
solutions to fix this problem..Thanks for all your advice.

shi

I don't know what I was thinking: a function can't change the selection or the active sheet. Try
your function this way:

Function GenMArray() As Variant
Dim myCell As Range
Set myCell = ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Offset(3, 1)

MsgBox "The address is " & myCell.Address
MsgBox "The array is " & Range(myCell, myCell.End(xlDown)).Address
GenMArray = Application.Transpose(Range(myCell, myCell.End(xlDown)))

End Function

HTH,
Bernie
MS Excel MVP


"shishi" wrote in message
oups.com...
Hi,

I have made my code exactly the same as the way Bernie has suggested,

but still it fails. I ran Debug-Step Into. When it executes the line
GenMArray = Application.Transpose(Range(Ac*tiveCell,
ActiveCell.End(xlDown))) ,
it come up with the Runtime Error '1004'- Application -
defined or Object - defined error. But if I place the mouse pointer
on top of the Range(Ac*tiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out
solutions to fix this problem..Thanks for all your advice.

shi

Bernie - can you clarify your statement?
There is no such limitation on a function if it is used as you illustrated
it - called by a vba procedure. If it is used as a UDF - used in a
worksheet cell, then it has that limitation.

Just some added considerations (though not the error cited):
Problems also may be because the number of cells in the range being
transposed is greater than 5461 (depends on the version of excel).

http://support.microsoft.com/?id=177991
XL: Limitations of Passing Arrays to Excel Using Automation (Q177991)
--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
I don't know what I was thinking: a function can't change the selection or
the active sheet. Try
your function this way:

Function GenMArray() As Variant
Dim myCell As Range
Set myCell =
ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Offset(3, 1)

MsgBox "The address is " & myCell.Address
MsgBox "The array is " & Range(myCell, myCell.End(xlDown)).Address
GenMArray = Application.Transpose(Range(myCell, myCell.End(xlDown)))

End Function

HTH,
Bernie
MS Excel MVP


"shishi" wrote in message
oups.com...
Hi,

I have made my code exactly the same as the way Bernie has suggested,

but still it fails. I ran Debug-Step Into. When it executes the line
GenMArray = Application.Transpose(Range(Ac*tiveCell,
ActiveCell.End(xlDown))) ,
it come up with the Runtime Error '1004'- Application -
defined or Object - defined error. But if I place the mouse pointer
on top of the Range(Ac*tiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out
solutions to fix this problem..Thanks for all your advice.

shi

Tom,

The original post didn't show how it was being called - I called it from a sub to test it, but then
later realized that the OP might be calling it from a worksheet. The only runtime error 1004 I was
able to generate was when I called the function with another worksheet active, but it was the
"Activate method of range class failed"....

The second msgbox was meant to show the address of the cells he was trying to transpose: I didn't
get the same error message as he was describing when I used a very large range.

Bernie
MS Excel MVP


"Tom Ogilvy" wrote in message ...
Bernie - can you clarify your statement?
There is no such limitation on a function if it is used as you illustrated
it - called by a vba procedure. If it is used as a UDF - used in a
worksheet cell, then it has that limitation.

Just some added considerations (though not the error cited):
Problems also may be because the number of cells in the range being
transposed is greater than 5461 (depends on the version of excel).

http://support.microsoft.com/?id=177991
XL: Limitations of Passing Arrays to Excel Using Automation (Q177991)
--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
I don't know what I was thinking: a function can't change the selection or
the active sheet. Try
your function this way:

Function GenMArray() As Variant
Dim myCell As Range
Set myCell =
ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULE S").Offset(3, 1)

MsgBox "The address is " & myCell.Address
MsgBox "The array is " & Range(myCell, myCell.End(xlDown)).Address
GenMArray = Application.Transpose(Range(myCell, myCell.End(xlDown)))

End Function

HTH,
Bernie
MS Excel MVP


"shishi" wrote in message
oups.com...
Hi,

I have made my code exactly the same as the way Bernie has suggested,

but still it fails. I ran Debug-Step Into. When it executes the line
GenMArray = Application.Transpose(Range(Ac*tiveCell,
ActiveCell.End(xlDown))) ,
it come up with the Runtime Error '1004'- Application -
defined or Object - defined error. But if I place the mouse pointer
on top of the Range(Ac*tiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out
solutions to fix this problem..Thanks for all your advice.

shi