Reading to a 1-D array...Error

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

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" <shishiram@gmail.com> wrote in message
news:1123554235.164383.157120@o13g2000cwo.googlegroups.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_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
>

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 =3D Application.Transpose(Range(Ac=ADtiveCell,
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=ADtiveCell, ActiveCell.End(xlDown)), part of the
stement,
I can see that it has read the actual values. Any clues...I am running
out=20
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_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


"shishi" <shishiram@gmail.com> wrote in message
news:1123601391.508471.244630@o13g2000cwo.googlegroups.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_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


"shishi" <shishiram@gmail.com> wrote in message
news:1123601391.508471.244630@o13g2000cwo.googlegroups.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
news:ecK7PrPnFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> "shishi" <shishiram@gmail.com> wrote in message
> news:1123601391.508471.244630@o13g2000cwo.googlegroups.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" <twogilvy@msn.com> wrote in message news:%23xfwVIQnFHA.3256@TK2MSFTNGP12.phx.gbl...
> 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
> news:ecK7PrPnFHA.3656@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>>
>> "shishi" <shishiram@gmail.com> wrote in message
>> news:1123601391.508471.244630@o13g2000cwo.googlegroups.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
>>
>>
>
>

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)


--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eqyYffQnFHA.1412@TK2MSFTNGP09.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
news:%23xfwVIQnFHA.3256@TK2MSFTNGP12.phx.gbl...
> > 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
> > news:ecK7PrPnFHA.3656@TK2MSFTNGP09.phx.gbl...
> >> 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
> >>
> >>
> >> "shishi" <shishiram@gmail.com> wrote in message
> >> news:1123601391.508471.244630@o13g2000cwo.googlegroups.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
> >>
> >>
> >
> >
>
>

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

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.

--
Regards,
Tom Ogilvy



"shishi" <shishiram@gmail.com> wrote in message
news:1123616446.335176.98690@g14g2000cwa.googlegroups.com...
> 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
>