PDA

View Full Version : elimanate zero while taking minimum value



shamsam1
07-28-2008, 05:18 AM
i have 4 columns ,format of cell is text
(10,2, , ,)

Dim dblMin As Double
dblMin = Application.Min(CDbl(Cells(1, 1)), CDbl(Cells(1, 2)), CDbl(Cells(1, 3),CDbl(Cells(1, 4)))

i want to take min of 4 columns , when i take min its shows zero to me i need to eliminate zero and consider 2 as mininum

please guide me regarding this ..with vba code bit urgent

Bob Phillips
07-28-2008, 05:19 AM
Dim dblMin As Double
dblMin = Activesheet.Evaluate("MIN(IF(A1:D1<>0,A1:D1))")

shamsam1
07-28-2008, 06:15 AM
code works fine if cell format is numeric or general
but when cell format is text value displayed is zero

still i am facing problem

Bob Phillips
07-28-2008, 06:20 AM
Be smart, change them to General not text



Dim dblMin As Double
dblMin = ActiveSheet.Evaluate("MIN(IF(A1:D1<>0,--(A1:D1)))")

shamsam1
07-28-2008, 07:10 AM
i cannt change cell value to numeric from text

if i can find second minimum value then my problem will be solved
but still stuck to find the minimum value

.

shamsam1
07-28-2008, 07:13 AM
thanks for solving my problem

shamsam1
07-28-2008, 08:26 AM
Dim dblMin As Double
dblMin = ActiveSheet.Evaluate("MIN(IF(cells(1,1):cells(1,4)<>0,--(cells(1,1):cells(1,4)
)))")

it shows type mismatch what i am doing wrong please guide me

Bob Phillips
07-28-2008, 08:46 AM
Look at what I gave you, compare against what you wrote, and it should be obvious.

shamsam1
07-28-2008, 09:15 PM
dim min as double
dblMin = Active Sheet.Evaluate("MIN(IF(A1:d1<>0,--(A1:d1)))")
please guide me to increment to (A2:d2<>0,--(A2:d2) so on
ie.i want to increment to next row

Bob Phillips
07-29-2008, 12:32 AM
Dim min As Double
Dim rng As Range
Set rng = Range("A1:D1")
dblMin = ActiveSheet.Evaluate("MIN(IF(" & rng.Address(False, False) & _
"<>0,--(" & rng.Address(False, False) & ")))")
Set rng = Range("A2:D2")
dblMin = ActiveSheet.Evaluate("MIN(IF(" & rng.Address(False, False) & _
"<>0,--(" & rng.Address(False, False) & ")))")

shamsam1
07-29-2008, 01:53 AM
thanks u ..My problem is solved:friends:

shamsam1
07-29-2008, 02:11 AM
Dim rng As Range
ii as integer
ii=1

Set rng = Range(Cells(ii, 1), Cells(ii, 4))

MinVal = ActiveSheet.Evaluate("MIN(IF(" & rng.Address(False, False) & _
"<>0,--(" & rng.Address(False, False) & ")))")

ii=ii+1

here i am incrementing (ii) value to increment to neat row.. and this works ..

:bow: ...THANKS