Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
Will, You need to dimension it properly, and then use set like so: Sub test() Dim myRange As Range Dim var1 As Range Set myRange = Range("A1:A3") Set var1 = myRange.Item(1) MsgBox var1.Address End Sub HTH, Bernie MS Excel MVP "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
I have done as you said, but the code below bombs when executing the line of code "oldshearvalue = myRange.Item(1)" What am I still doing wrong? Public Function maxmoment(myRange As Range) Dim shearvalue As Range, oldshearvalue As Range, momentvalue1, momentvalue2, maxmomentvalue, maxmomentvalues(), index As Long, currentmax As Double oldshearvalue = myRange.Item(1) For Each shearvalue In myRange.Cells If oldshearvalue.Value * shearvalue.Value < 0 Then momentvalue1 = oldshearvalue.Offset(0, -1) momentvalue2 = shearvalue.Offset(0, -1) If momentvalue1 momentvalue2 Then maxmomentvalue = momentvalue1 Else maxmomentvalue = momentvalue2 End If ReDim Preserve maxmomentvalues(UBound(maxmomentvalues) + 1) maxmomentvalues(UBound(maxmomentvalues)) = maxmomentvalue End If oldshearvalue = shearvalue Next For index = LBound(maxmomentvalues) To UBound(maxmomentvalues) If maxmomentvalues(index) currentmax Then currentmax = maxmomentvalues(index) End If Next index maxmoment = currentmax End Function "Bernie Deitrick" wrote: Will, You need to dimension it properly, and then use set like so: Sub test() Dim myRange As Range Dim var1 As Range Set myRange = Range("A1:A3") Set var1 = myRange.Item(1) MsgBox var1.Address End Sub HTH, Bernie MS Excel MVP "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
Sub dk() Dim MyRange As Range, var1 Set MyRange = ActiveSheet.Range("B2:E2") var1 = MyRange.Item(1).Address MsgBox var1 End Sub "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
That actually only returns a string for the cell. You would have to use: Range(var1) = 'something as a range object. "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
How about dim myRange as range dim Var1 as range set myrange = activesheet.range("A1:z99") set var1 = myrange.cells(1) You declared var1 as a variant. And you didn't use "Set", so VBA thought you wanted the default property of the range. maweilian wrote: Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
I have tried what you suggested below but the line: "oldshearvalue = Range(myRange.Item(1).Address)" still seems to be returning a Double and not a Range object. Public Function maxmoment(myRange As Range) Dim shearvalue, oldshearvalue, momentvalue1, momentvalue2, maxmomentvalue, maxmomentvalues(), index As Long, currentmax As Double oldshearvalue = Range(myRange.Item(1).Address) For Each shearvalue In myRange.Cells If oldshearvalue.Value * shearvalue.Value < 0 Then momentvalue1 = oldshearvalue.Offset(0, -1) momentvalue2 = shearvalue.Offset(0, -1) If momentvalue1 momentvalue2 Then maxmomentvalue = momentvalue1 Else maxmomentvalue = momentvalue2 End If ReDim Preserve maxmomentvalues(UBound(maxmomentvalues) + 1) maxmomentvalues(UBound(maxmomentvalues)) = maxmomentvalue End If oldshearvalue = shearvalue Next For index = LBound(maxmomentvalues) To UBound(maxmomentvalues) If maxmomentvalues(index) currentmax Then currentmax = maxmomentvalues(index) End If Next index maxmoment = currentmax End Function "JLGWhiz" wrote: That actually only returns a string for the cell. You would have to use: Range(var1) = 'something as a range object. "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
Problem fixed! Thanks so much! "Dave Peterson" wrote: How about dim myRange as range dim Var1 as range set myrange = activesheet.range("A1:z99") set var1 = myrange.cells(1) You declared var1 as a variant. And you didn't use "Set", so VBA thought you wanted the default property of the range. maweilian wrote: Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a Range object
I could be misunderstanding what you are looking for. If you Dim the variable as Range, then set that variable to equal a range object, the variable is in effect a range object. The value of the object and it's data type will be subject to the content of the range. What are you expecting the data type to be? "maweilian" wrote in message ... I have tried what you suggested below but the line: "oldshearvalue = Range(myRange.Item(1).Address)" still seems to be returning a Double and not a Range object. Public Function maxmoment(myRange As Range) Dim shearvalue, oldshearvalue, momentvalue1, momentvalue2, maxmomentvalue, maxmomentvalues(), index As Long, currentmax As Double oldshearvalue = Range(myRange.Item(1).Address) For Each shearvalue In myRange.Cells If oldshearvalue.Value * shearvalue.Value < 0 Then momentvalue1 = oldshearvalue.Offset(0, -1) momentvalue2 = shearvalue.Offset(0, -1) If momentvalue1 momentvalue2 Then maxmomentvalue = momentvalue1 Else maxmomentvalue = momentvalue2 End If ReDim Preserve maxmomentvalues(UBound(maxmomentvalues) + 1) maxmomentvalues(UBound(maxmomentvalues)) = maxmomentvalue End If oldshearvalue = shearvalue Next For index = LBound(maxmomentvalues) To UBound(maxmomentvalues) If maxmomentvalues(index) currentmax Then currentmax = maxmomentvalues(index) End If Next index maxmoment = currentmax End Function "JLGWhiz" wrote: That actually only returns a string for the cell. You would have to use: Range(var1) = 'something as a range object. "maweilian" wrote in message ... Another newbie question: For the following: Dim myRange as Range, var1 var1 = myRange.Item(1) MyRange.Item(1) returns a Variant/Double. How can I get it to return a range object representing a single cell? Thanks, Will |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop first row in range and return a new range object | Excel Programming | |||
returning pivottable object from a range object | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |