Range B3:D21 is an array, not a single value. This will work
Sub Test()
Debug.Print Examine_a_Range("Sheet1", "a1")
End Sub
Function Examine_a_Range(ws, rng) As String
Dim mySheet As Worksheet
Dim myRange As Range
myrtn = Worksheets(ws).Range(rng).Value
Examine_a_Range = myrtn
End Function
This may assist you with your problem or displaying more values than a
single cell. Depending on your stage of learning, you may find it difficult
to grasp - I know I did :-( The good side of this is that, if I learned it,
so can you.
http://support.microsoft.com/kb/213798
Broadly speaking, think of an array as a collection of pigeon holes. Copy a
range from workbook creates the set of pigeon holes just the right size and
fills them. This is a little different to normal array usage when you have
to specify how big to make the array before you use it. When you have items
in the array, you can recall the value by specifying its address in the
array.
Looking at how to retrieve values from your range:
Sub TestArray()
Dim myarray As Variant
myarray = Range("B3:D21").Value
'Looping structure to look at array.
For rw = 1 To 19 'rows
For cl = 1 To 3 ' columns
MsgBox myarray(rw, cl)
Next cl
Next rw
End Sub
--
Steve
"keith" wrote in message
...
Hello,
The worksheets property under €śhelp€ť describes ranges by using the
following
example,
MsgBox Worksheets("Sheet1").Range("A1").Value
In my code, I am using two forms of it. They are€¦
Worksheets(1).Range("c5:c10").Cells(1, 1).Formula = "=rand()"
And
Debug.Print Examine_a_Range (Worksheets("Sheet1").Range("B3:D21"))
The function €śExamine_a_Range€ť has opening line of €¦
Function Examine_a_Range(Rng0 As Range) As Variant
Some code here..
End function
Now that Ive learned how to obtain the cell range from the RefEdit
control,
I see the form of a cell range from RefEdit is€¦
Sheet1!$E$9:$F$11
If I want to use the value from the RefEdit control in my code, do I have
to
go through some text editing work to split up the value from RefEdit, or
is
there a better way to assign the value to a Range and make it all work?
Thank you,
Keith