Reading to a 1-D array...Error

[shishi]

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_MODULES").Activate
ActiveCell.Offset(3, 1).Select

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

End Function

Thanks,
shi

 

[Bernie Deitrick]

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_MODULES").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]

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 Deitrick]

What does the second msgbox say:

Function GenMArray() As Variant

ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULES").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


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 Deitrick]

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_MODULES").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


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 Ogilvy]

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

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_MODULES").Offset(3, 1)

 

[Bernie Deitrick]

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]

Just a thought:

If I put No_of_Modules

on another sheet rather than summary and run

Sub abc()
ThisWorkbook.Sheets("SUMMARY").Range("NO_OF_MODULES").Activate

End Sub

I get his error message.

If I delete the defined name "NO_OF_MODULES", I get his error message.

( he could have NO_Of_MODULE = "B9" in his code and it actually isn't a
defined range/name)

 

[shishi]

Hi Bernie and Tom,

Thank you so much for discussing the issue that I have posted. By
going through your discussion I could learn a lot. I think I will add
some more to this discussion. The SUMMARY sheet looks somewhat like
this.

No. of Modules: 12( The cell where 12 appears is a named range with the
name NO_OF_MODULES.)
Then comes a list of modules

MODULE1
MODULE2
MODULE3 etc to 12.

I am writing a code to generate a sheet named SPECS. In SPECS I need
the list of modules from the SUMMARY sheet as an array.I modified the
function the way bernie's last post. that did really work. thanks a lot
bernie.

The reason for the error is : The SPECS sheets is active and and the
function was trying will make SUMMARY active for sometime to fetch the
data and then come back to SPECS sheet and make it active to complete
the rest of the code. I guess, that is not possible, right? I have a
similar situation,where I am copiying a range of cells from one sheet
to another. I tried a similar solution that worked in this case, but
unfortunately it didn't work. So I will be posting that to get some
advice from you all. Once again thank you very much Bernie and Tom.

Shishi

 

[Tom Ogilvy]

Function GenMArray() As Variant

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

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

End Function

If you turn on the macro recorder and go from sheet1 to sheet2, copy, then
come back to sheet1 and paste, it will do exactly what you describe.

Sheets("SUMMARY").Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Specs").Select
Range("C5").Select
ActiveSheet.Paste

but as Bernie implied, there is no reason to do all that.

With Worksheets("Summary")
.Range(.Range("No_Of_Modules"), _
.Range("No_Of_Modules").End(xldown)).Copy _
Destination:=Worksheets("SPECS").Range("A1")
End With

would get the job done faster without the screen flashing all over the
place.