finding min with currency
 

here is my original question

ok here is what i have.

A B C D E
1 V W X Y
2 1.95 1.55 1.75 2.05 #Display


now in cell E2 i want it to find the minimum value from A2 to D2 and
display the letter above that minimum values column found in row 1
(V,W,X or Y).


so in this example i want W to be displayed in Cell E2.

and this is the reponse i got


Sub test()
Dim rng As Range
Worksheets("Sheet1").Range("A2:D2").Select
Set rng = Selection.Find(What:=Application.min(Selection),
Lookat:=xlWhole, LookIn:=xlValues)
Range("E2") = rng.Offset(-1, 0)
End Sub

and it does work but now when i format the cells with the numbers in
them to currency it gives me a runtime error of 91. what do i need to
change in the code for it to work?

thank you
jonathan

finding min with currency
 

Maybe you could use a worksheet formula in E2:

=INDEX(A1:D1,MATCH(MIN(A2:D2),A2:D2,0))

Or try changing lookin:=xlvalues to lookin:=xlformulas

I think I'd drop the .select's, too:

Option Explicit
Sub test()
Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("A2:D2")
Set myCell = myRng.Find(What:=Application.Min(myRng), _
Lookat:=xlWhole, LookIn:=xlFormulas)
.Range("E2") = myCell.Offset(-1, 0)
End With
End Sub

wrote:

here is my original question

ok here is what i have.

A B C D E
1 V W X Y
2 1.95 1.55 1.75 2.05 #Display

now in cell E2 i want it to find the minimum value from A2 to D2 and
display the letter above that minimum values column found in row 1
(V,W,X or Y).

so in this example i want W to be displayed in Cell E2.

and this is the reponse i got

Sub test()
Dim rng As Range
Worksheets("Sheet1").Range("A2:D2").Select
Set rng = Selection.Find(What:=Application.min(Selection),
Lookat:=xlWhole, LookIn:=xlValues)
Range("E2") = rng.Offset(-1, 0)
End Sub

and it does work but now when i format the cells with the numbers in
them to currency it gives me a runtime error of 91. what do i need to
change in the code for it to work?

thank you
jonathan

--

Dave Peterson