indices in named range
 

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

indices in named range
 

I found a solution:

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


Is this the best solution?

Stefi


€žStefi€� ezt Ã*rta:

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

indices in named range
 

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)

"Stefi" wrote in message
...
I found a solution:

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


Is this the best solution?

Stefi


"Stefi" ezt írta:

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

indices in named range
 

Sorry, of course

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

Stefi


€žStefi€� ezt Ã*rta:

I found a solution:

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


Is this the best solution?

Stefi


€žStefi€� ezt Ã*rta:

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

indices in named range
 

Stefi,
So you have one way, or

Debug.Print ECell.Address(False, False, xlR1C1, , Range("B4:D5").Cells(1))

NickHK

"Stefi" wrote in message
...
I found a solution:

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


Is this the best solution?

Stefi


"Stefi" ezt írta:

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

indices in named range
 

Try...

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

Ken Johnson

indices in named range
 

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


€žBob Phillips€� ezt Ã*rta:

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)

"Stefi" wrote in message
...
I found a solution:

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


Is this the best solution?

Stefi


"Stefi" ezt Ã*rta:

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

indices in named range
 

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

Ken Johnson

indices in named range
 

Hi Nick,

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

Stefi


€žNickHK€� ezt Ã*rta:

Stefi,
So you have one way, or

Debug.Print ECell.Address(False, False, xlR1C1, , Range("B4:D5").Cells(1))

NickHK

"Stefi" wrote in message
...
I found a solution:

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


Is this the best solution?

Stefi


"Stefi" ezt Ã*rta:

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

indices in named range
 

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:

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

Ken Johnson

indices in named range
 

On Jul 3, 10:14 pm, Stefi wrote:
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:

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

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

indices in named range
 

On Jul 3, 10:39 pm, Ken Johnson wrote:
On Jul 3, 10:14 pm, Stefi wrote:

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:

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

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

indices in named range
 

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


€žKen Johnson€� ezt Ã*rta:

On Jul 3, 10:14 pm, Stefi wrote:
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 mrta:

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

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

indices in named range
 

would this give you the result you want?

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

--


Gary


"Stefi" wrote in message
...
Thanks, Ken, now it's clear. It's tricky!
Stefi


"Ken Johnson" ezt írta:

On Jul 3, 10:14 pm, Stefi wrote:
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 mrta:

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

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

indices in named range
 

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)

"Stefi" wrote in message
...
Thanks Bob for confirming my solution! What about my correction?
Stefi


"Bob Phillips" ezt írta:

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)

"Stefi" wrote in message
...
I found a solution:

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


Is this the best solution?

Stefi


"Stefi" ezt írta:

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

indices in named range
 

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:

would this give you the result you want?

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

--


Gary


"Stefi" wrote in message
...
Thanks, Ken, now it's clear. It's tricky!
Stefi


"Ken Johnson" ezt Ã*rta:

On Jul 3, 10:14 pm, Stefi wrote:
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 mrta:

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

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