How to return a Range object

[maweilian]

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

 

[Bernie Deitrick]

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]

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

 

[JLGWhiz]

Sub dk()
Dim MyRange As Range, var1
Set MyRange = ActiveSheet.Range("B2:E2")
var1 = MyRange.Item(1).Address
MsgBox var1
End Sub

 

[JLGWhiz]

That actually only returns a string for the cell. You would have to use:

Range(var1) = 'something

as a range object.

 

[Dave Peterson]

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]

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

 

[maweilian]

Problem fixed! Thanks so much!

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.

 

[JLGWhiz]

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?