Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem getting results of RefEdit into a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem getting results of RefEdit into a range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem getting results of RefEdit into a range
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...00906ACNJ.html _______ "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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem getting results of RefEdit into a range
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem getting results of RefEdit into a range
Thanks to Dave and all who provided answers to this question.
Keith "Dave Peterson" wrote: 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 wrote: 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 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refedit very serious/urgent problem | Excel Programming | |||
Problem with RefEdit Control in VBA | Excel Programming | |||
Strange RefEdit Problem | Excel Programming | |||
Refedit copy paste problem? | Excel Programming | |||
RefEdit in Class Module Problem | Excel Programming |