I lift a worksheet range into an array and determine its maximum but it
doesn't work, the results is always zero (its initial value):

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant
currencyarray = Range("A1:C1").Value
currencymax = Application.Max(currencyarray)
MsgBox currencymax
End Sub

If I first convert the cells number format (manually in the worksheet
itself) from Currency to General or Number than it *does* work.

So I thought of converting the complete currency array (in VBA, like to
singles) but I don't see a way how to do that. One possibility I guess would
be to iterate through each array element but this seems a long detour (I need
to work this on many long currency ranges).

Is there a simpler solution? Thanks very much!

-Joni

Dear Joni

Please try the below

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant

Set currencyarray = Range("A1:C1")
currencymax = WorksheetFunction.Max(currencyarray)
MsgBox currencymax

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"jonigr" wrote:

I lift a worksheet range into an array and determine its maximum but it
doesn't work, the results is always zero (its initial value):

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant
currencyarray = Range("A1:C1").Value
currencymax = Application.Max(currencyarray)
MsgBox currencymax
End Sub

If I first convert the cells number format (manually in the worksheet
itself) from Currency to General or Number than it *does* work.

So I thought of converting the complete currency array (in VBA, like to
singles) but I don't see a way how to do that. One possibility I guess would
be to iterate through each array element but this seems a long detour (I need
to work this on many long currency ranges).

Is there a simpler solution? Thanks very much!

-Joni

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant
currencyarray = Range("A1:C1").Value
currencymax = Application.Max(currencyarray)
MsgBox currencymax
End Sub

Your code (above) worked correctly for me if the numbers you are looking for
are on the active sheet. Perhaps it would be better to qualify your Range
reference with the Worksheet you want to get it from. Assign your value to
currencyarray like this (where I have assumed the cells you are interested
in are on Sheet1)...

currencyarray = Worksheets("Sheet1").Range("A1:C1").Value

--
Rick (MVP - Excel)


"jonigr" wrote in message
...
I lift a worksheet range into an array and determine its maximum but it
doesn't work, the results is always zero (its initial value):

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant
currencyarray = Range("A1:C1").Value
currencymax = Application.Max(currencyarray)
MsgBox currencymax
End Sub

If I first convert the cells number format (manually in the worksheet
itself) from Currency to General or Number than it *does* work.

So I thought of converting the complete currency array (in VBA, like to
singles) but I don't see a way how to do that. One possibility I guess
would
be to iterate through each array element but this seems a long detour (I
need
to work this on many long currency ranges).

Is there a simpler solution? Thanks very much!

-Joni

Currency and Dates are strange.

Try:

currencyarray = Range("A1:C1").Value2

VBA's help on Value2 will explain, er, describe the situation.

jonigr wrote:

I lift a worksheet range into an array and determine its maximum but it
doesn't work, the results is always zero (its initial value):

Sub curtest()
Dim currencyarray As Variant
Dim currencymax As Variant
currencyarray = Range("A1:C1").Value
currencymax = Application.Max(currencyarray)
MsgBox currencymax
End Sub

If I first convert the cells number format (manually in the worksheet
itself) from Currency to General or Number than it *does* work.

So I thought of converting the complete currency array (in VBA, like to
singles) but I don't see a way how to do that. One possibility I guess would
be to iterate through each array element but this seems a long detour (I need
to work this on many long currency ranges).

Is there a simpler solution? Thanks very much!

-Joni

--

Dave Peterson

Wow, great help!

Both Jacob's and Dave's suggestions work, Very similar apparently, both turn
currencymax into a double (that's fine).

Thanks very much!

-Joni