Problem getting results of RefEdit into a range

[keith]

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("B321"))
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 I’ve 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

 

[AltaEgo]

Range B321 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("B321").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

 

[Jon Peltier]

You can use

Range("Sheet1!$E$9:$F$11")

or

Range(RefEdit1.Text)

to define the range, without having to parse out the worksheet and address.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Dave Peterson]

You could try something like this, too:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.Range(Me.RefEdit1.Value)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "not a valid range"
Else
MsgBox myRng.Address(external:=True)
End If

End Sub

 

[keith]

Thanks to Dave and all who provided answers to this question.
Keith