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

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

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

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

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

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

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

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

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