Returning the row no of the lowest non-zero cell in a range
 

Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought of a
nested loop where the value of each cell is tested and the row number stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Two steps:

1. determine the lowest non-zero number: =SMALL(B10:B25,1) and if this is
zero use =SMALL(B10:B25,2)

2. find the row: use MATCH() on the result of step 1
--
Gary's Student


"Peter Rooney" wrote:

Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought of a
nested loop where the value of each cell is tested and the row number stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Pete,

Use

=MATCH(MIN(IF(B10:B25<0,B10:B25)),B10:B25,0)

which is an array formula, so commit with Ctrl-Shift-Enter. Be aware, it
will find the first if there are duplicates.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" wrote in message
...
Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought
of a
nested loop where the value of each cell is tested and the row number
stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Sorry, I didn't make myself clear.

The purpose of the exercise is to determine how far down a worksheet a
"Cumulative Hours" formula needs to be copied, so I don't have a column
containing unnecessary identical cumulative values where there isn't a
corresponding actual (the X axis, actuals and cumulatives are in columns B, C
and D)
I'm trying to do this in VBA - not in the actual worksheet.
Sorry if I wasted your time before!

Pete





"Gary''s Student" wrote:

Two steps:

1. determine the lowest non-zero number: =SMALL(B10:B25,1) and if this is
zero use =SMALL(B10:B25,2)

2. find the row: use MATCH() on the result of step 1
--
Gary's Student


"Peter Rooney" wrote:

Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought of a
nested loop where the value of each cell is tested and the row number stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Bob,

Sorry, I didn't make myself clear.

The purpose of the exercise is to determine how far down a worksheet a
"Cumulative Hours" formula needs to be copied, so I don't have a column
containing unnecessary identical cumulative values where there isn't a
corresponding actual (the X axis, actuals and cumulatives are in columns B, C
and D)
I'm trying to do this in VBA - not in the actual worksheet.
Sorry if I wasted your time before!

Pete


"Bob Phillips" wrote:

Pete,

Use

=MATCH(MIN(IF(B10:B25<0,B10:B25)),B10:B25,0)

which is an array formula, so commit with Ctrl-Shift-Enter. Be aware, it
will find the first if there are duplicates.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" wrote in message
...
Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought
of a
nested loop where the value of each cell is tested and the row number
stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Sorry, I didn't make myself clear.

The purpose of the exercise is to determine how far down a worksheet a
"Cumulative Hours" formula needs to be copied, so I don't have a column
containing unnecessary identical cumulative values where there isn't a
corresponding actual (the X axis, actuals and cumulatives are in columns B, C
and D)
I'm trying to do this in VBA - not in the actual worksheet.
Sorry if I wasted your time before!

Pete


"Gary''s Student" wrote:

Two steps:

1. determine the lowest non-zero number: =SMALL(B10:B25,1) and if this is
zero use =SMALL(B10:B25,2)

2. find the row: use MATCH() on the result of step 1
--
Gary's Student


"Peter Rooney" wrote:

Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought of a
nested loop where the value of each cell is tested and the row number stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

iRow = Evaluate("MATCH(MIN(IF(B10:B25<0,B10:B25)),B10:B2 5,0)")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" wrote in message
...
Bob,

Sorry, I didn't make myself clear.

The purpose of the exercise is to determine how far down a worksheet a
"Cumulative Hours" formula needs to be copied, so I don't have a column
containing unnecessary identical cumulative values where there isn't a
corresponding actual (the X axis, actuals and cumulatives are in columns
B, C
and D)
I'm trying to do this in VBA - not in the actual worksheet.
Sorry if I wasted your time before!

Pete


"Bob Phillips" wrote:

Pete,

Use

=MATCH(MIN(IF(B10:B25<0,B10:B25)),B10:B25,0)

which is an array formula, so commit with Ctrl-Shift-Enter. Be aware, it
will find the first if there are duplicates.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" wrote in message
...
Good morning all,

Can anyone suggest a quick way of returning the row number of the
lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I
thought
of a
nested loop where the value of each cell is tested and the row number
stored
is to a varable if it is greater than the value already stored there,
but
this needs to be part of a worksheet_change macro and it might slow
things
down too much doing things in this way.

Thanks in advance

Pete

Returning the row no of the lowest non-zero cell in a range
 

Sub MinRow()
Dim rng As Range
Dim MyRow As Long
Dim MyFormula As String

Set rng = Sheet1.Range("B10:B25")
MyFormula = WorksheetFunction.Match(WorksheetFunction.Small _
(rng, WorksheetFunction.CountIf(rng, 0) + 1), rng, 0)
MyRow = MyFormula + 9
End Sub

Mike F
"Peter Rooney" wrote in message
...
Good morning all,

Can anyone suggest a quick way of returning the row number of the lowest
cell in a range (e.g. B10:B25) that contains a non-zero value? I thought
of a
nested loop where the value of each cell is tested and the row number
stored
is to a varable if it is greater than the value already stored there, but
this needs to be part of a worksheet_change macro and it might slow things
down too much doing things in this way.

Thanks in advance

Pete