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

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

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

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

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

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

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

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