indices in named range

[Guest]

Hi All,

I have a For each cycle, like this:

For Each ecell In erng

Next ecell

I can't figure out how to determine row and column indices of ECELL inside
the cycle. I mean that I'd like to know for example, that the current ECELL
is in the 3rd row and 2nd column of ERNG.

Please help! Thanks,
Stefi

 

[Guest]

I found a solution:

ERNG.Row - ECELL.Row + 1
ERNG.Column - ECELL.Column + 1


Is this the best solution?

Stefi


„Stefi†ezt írta:

 

[Bob Phillips]

That's the right way to do assuming that erng may not always start in row 1
and/or column 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Sorry, of course

ECELL.Row - ERNG.Row + 1
ECELL.Column - ERNG.Column + 1

Stefi


„Stefi†ezt írta:

 

[NickHK]

Stefi,
So you have one way, or

Debug.Print ECell.Address(False, False, xlR1C1, , Range("B45").Cells(1))

NickHK

 

[Ken Johnson]

Try...

Range(erng(1), ecell).Rows.Count
Range(erng(1), ecell).Columns.Count

Ken Johnson

 

[Guest]

Thanks Bob for confirming my solution! What about my correction?
Stefi


„Bob Phillips†ezt írta:

 

[Ken Johnson]

Better still Stefi, don't bother with mine, yours works fine.

Ken Johnson

 

[Guest]

Hi Nick,

I tried this way as well (I supposed that Range("B45") should be
substituted with my range ERNG) but it returned the string "RC", I couldn't
find out why.

Stefi


„NickHK†ezt írta:

 

[Guest]

Thanks Ken, nonetheless I tried Your way, too, it works fine, but I'm still
working hard to understand its logic, I like to learn new techniques.

Stefi


„Ken Johnson†ezt írta:

 

[Ken Johnson]

Thanks Ken, nonetheless I tried Your way, too, it works fine, but I'm still
working hard to understand its logic, I like to learn new techniques.

Stefi

,,Ken Johnson" ezt írta:

Hi Stefi,

erng(1) is the top-left cell of erng.
Range(erng(1), ecell) is a range with erng(1) the top-left cell and
ecell the bottom-right cell.
So, it's then a matter of counting the rows and columns in
Range(erng(1), ecell) to get ecell's row and column index within that
range.

I think your way is better because it doesn't use the Count function.

Ken Johnson

 

[Ken Johnson]

Hi Stefi,

erng(1) is the top-left cell of erng.
Range(erng(1), ecell) is a range with erng(1) the top-left cell and
ecell the bottom-right cell.
So, it's then a matter of counting the rows and columns in
Range(erng(1), ecell) to get ecell's row and column index within that
range.

I think your way is better because it doesn't use the Count function.

Ken Johnson

Oops,

I meant...

So, it's then a matter of counting the rows and columns in
Range(erng(1), ecell) to get ecell's row and column index within erng.

Ken Johnson

 

[Guest]

Thanks, Ken, now it's clear. It's tricky!
Stefi


„Ken Johnson†ezt írta:

 

[Gary Keramidas]

would this give you the result you want?

ECELL.Row - erng.Row + erng.Row
ECELL.Column - erng.Column + erng.Row

 

[Bob Phillips]

I assumed the error and read it as corrected.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Gary,

Please, clarify your formulae!
ECELL.Row - erng.Row + erng.Row obviously equals to ECELL.Row, which is not
the required result,
and I guess, that
ECELL.Column - erng.Column + erng.Row is a typo
(instead of ECELL.Column - erng.Column + erng.Column)
In this case the problem is the same as with the first formula.

Regards,
Stefi


„Gary Keramidas†ezt írta: