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