Help with Range Probelm PLEASE !

[Dan Thompson]

Ok here is my problem I am using the DirectPrecedents method to return an
address sometimes that address will be one cell sometimes it will be a range
of cells. What I was Going to do is have the address(String) returned by the
DirectPrecedents (ie.. "$C$5:$C$10") Parsed using some other code so that I
could chop out the row values from the string (ie 5 and 10) and than convert
them to integers, subtract them from each other and if the difference is
greater than 0 that would let me know if the DirectPrecedents for the cell
selected has one or more than one Precedents linked to it.




Dim CurrentCel
Dim X

With ActiveSheet
CurrentCel = ActiveCell.Address
X = .Range(CurrentCel).DirectPrecedents.Address
'This is the part I need to some how say If the range of X is more than
'one cell than (Run this code) else (Run that code)
I was going to put somthing like this
Z=0
For each cel in X
Z = Z + 1
next
but I cant do that because VBA doesn't see X as Range only as string.
this would be much simpler than using a string parsing code and converting
the values in the string to Integers just to preform some math so vba can
tell me if the range returned in string form form the DirectPrecedents method
is greater than one cel.

Help Please there has to be an easyer way ?

Dan Thompson


End With

 

[Jim Cone]

You are making it too hard.
DirectPrecedents returns a range object. So...
'--
Dim X as Range
Dim Z as Long
Set X = Range(CurrentCel).DirectPrecedents
Z = X.Cells.Count
'--
Jim Cone
Portland, Oregon USA


"Dan Thompson"
<[email protected]>
wrote in message
Ok here is my problem I am using the DirectPrecedents method to return an
address sometimes that address will be one cell sometimes it will be a range
of cells. What I was Going to do is have the address(String) returned by the
DirectPrecedents (ie.. "$C$5:$C$10") Parsed using some other code so that I
could chop out the row values from the string (ie 5 and 10) and than convert
them to integers, subtract them from each other and if the difference is
greater than 0 that would let me know if the DirectPrecedents for the cell
selected has one or more than one Precedents linked to it.




Dim CurrentCel
Dim X

With ActiveSheet
CurrentCel = ActiveCell.Address
X = .Range(CurrentCel).DirectPrecedents.Address
'This is the part I need to some how say If the range of X is more than
'one cell than (Run this code) else (Run that code)
I was going to put somthing like this
Z=0
For each cel in X
Z = Z + 1
next
but I can't do that because VBA doesn't see X as Range only as string.
this would be much simpler than using a string parsing code and converting
the values in the string to Integers just to preform some math so vba can
tell me if the range returned in string form form the DirectPrecedents method
is greater than one cel.
End With
Help Please there has to be an easyer way ?
Dan Thompson

 

[Jim Cone]

Further...
There is code of mine to return precedents/dependents
in this post from January...
http://tinyurl.com/celfut

 

[Dan Thompson]

Thanks Jim your advice was of great help to me and much simpler too.
I new there was an easier way. I was having trouble finding information
yesterday on what different methods in vba will return is there a simple list
in the help file that will say this method will return these objects ect..

Again thanks for your help Jim
You ROCK !

 

[Jim Cone]

You are welcome.
As for a "simple list" in the help file, the answer is no.
In the case of "DirectPrecedents", the help file states that it returns
a range object.
Looking up "Range" then gives you the long list of properties and
methods that apply to a range.
Range properties and methods are the ones to become very familiar with.
(maybe make your own list)

Note that Range has a count property, but I used X.Cells.Count not
X.Count. The range returned could have consisted of entire columns
or entire rows and the X.Count property, in those cases, would have
returned the columns count or rows count (not the count of cells).
--
Jim Cone
Portland, Oregon USA




"Dan Thompson"
<[email protected]>
wrote in message
Thanks Jim your advice was of great help to me and much simpler too.
I new there was an easier way. I was having trouble finding information
yesterday on what different methods in vba will return is there a simple list
in the help file that will say this method will return these objects ect..

Again thanks for your help Jim
You ROCK !