How to return a Range object

  • Thread starter Thread starter maweilian
  • Start date Start date
M

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
 
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
 
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
 
Sub dk()
Dim MyRange As Range, var1
Set MyRange = ActiveSheet.Range("B2:E2")
var1 = MyRange.Item(1).Address
MsgBox var1
End Sub
 
That actually only returns a string for the cell. You would have to use:

Range(var1) = 'something

as 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.
 
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
 
Problem fixed! Thanks so much!

Dave Peterson said:
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.
Click to expand...
 
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?
 
Back
Top