How do I determine the lowest non-zero value in a range?

This works if the lowest value is NOT zero:

dblMin = xlapp.Min(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile ).
Worksheets(sn(i)).Cells(2, 4), xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(lr, lc)))
[lr = last row, lc = last column]

Is there some way to ignore zero values?

Here is an example

Debug.Print Evaluate("MIN(IF(A1:A10<0,A1:A10))")

--

HTH

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


"deko" wrote in message
om...
How do I determine the lowest non-zero value in a range?

This works if the lowest value is NOT zero:

dblMin = xlapp.Min(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile ).
Worksheets(sn(i)).Cells(2, 4), xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(lr, lc)))
[lr = last row, lc = last column]

Is there some way to ignore zero values?

Debug.Print Evaluate("MIN(IF(A1:A10<0,A1:A10))")

Correct me if I'm wrong, but I believe this is saying: "If there is no zero
value in range A1:A10, then return the minimum value in the range". But
what if there *is* a zero value? How then would I get the minimum non-zero
value in the range?

In Access VBA, I think it would look like this:

MinVal = DMin("fldName", "tblName", "fldName 0")

But all I have is a range in an Excel worksheet.

Perhaps I could assign the min value in the range to a variable, then test
the variable and get the average:

If MinVal = 0 Then
dblMin = xlapp.Average(myRange)
End if

Then increment down until 0

Do While MinVal 0
MinVal = MinVal - x
Loop

But this could be inefficient - especially if I don't know how much to
increment down with each loop. Perhaps I could guess somehow by getting the
max and/or the standard deviation? The reason I need the min value is to
set the Y-axis on a chart - so it does not have to be absolutely precise,
but it does need to be pretty close.

Other ideas?

Thanks for the help.

No it is saying take the minimum of all values in the rfange that are not
zero.

Why don't you try it?

--

HTH

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


"deko" wrote in message
om...
Debug.Print Evaluate("MIN(IF(A1:A10<0,A1:A10))")

Correct me if I'm wrong, but I believe this is saying: "If there is no
zero
value in range A1:A10, then return the minimum value in the range". But
what if there *is* a zero value? How then would I get the minimum
non-zero
value in the range?

In Access VBA, I think it would look like this:

MinVal = DMin("fldName", "tblName", "fldName 0")

But all I have is a range in an Excel worksheet.

Perhaps I could assign the min value in the range to a variable, then test
the variable and get the average:

If MinVal = 0 Then
dblMin = xlapp.Average(myRange)
End if

Then increment down until 0

Do While MinVal 0
MinVal = MinVal - x
Loop

But this could be inefficient - especially if I don't know how much to
increment down with each loop. Perhaps I could guess somehow by getting
the
max and/or the standard deviation? The reason I need the min value is to
set the Y-axis on a chart - so it does not have to be absolutely precise,
but it does need to be pretty close.

Other ideas?

Thanks for the help.

No it is saying take the minimum of all values in the rfange that are not
zero.

I see. I should know by now not to second guess you...

Debug.Print Evaluate("MIN(IF(xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Range(xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Cells(lr, lc))) < 0, xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)).Cells(lr, lc))))")

Result: Error 2015

Is this caused by the input string being too long?

Yes, Evaluate does have limitations, the length of the string being one
of them. However, in this case, it is something else.

Evaluate argument must be an XL formula. XL doesn't understand any
Workbooks mumbo-jumbo, which is VBA-speak. You need to convert your
range into a XL-type range reference, i.e.,
MIN(IF('[Book1]Sheet 1'!$A$1:$A$16<0,'[Book1]Sheet 1'!$A$1:$A$16))

Use something like the untested:

dim RngAddr as string
....
with xlapp.workbooks(strxlsfile).worksheets(sn(i))
rngaddr="'[" & .parent.name & "]" & .name & "'!"
RngAddr=rngaddr & .Range(.Cells(2, 4),.Cells(lr, lc))).address
end with
debug.print xlapp.evaluate( _
"MIN(IF(" & rngaddr & "<0," & rngaddr & "))")
....

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
No it is saying take the minimum of all values in the rfange that are not
zero.

I see. I should know by now not to second guess you...

Debug.Print Evaluate("MIN(IF(xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Range(xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)). _
Cells(lr, lc))) < 0, xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 4), xlapp. _
Workbooks(strXlsFile).Worksheets(sn(i)).Cells(lr, lc))))")

Result: Error 2015

Is this caused by the input string being too long?