Hello I am trying to get the MIN and MAX values from the array and it always returns "0" despite anything. My code:
Dim MachineCapacitySmallestArray() As Variant MachineCapacitySmallestArray = thisworkbook.worksheets(1).range("C25:D25") SmallestCapacity = Application.Min(MachineCapacitySmallestArray)
in range I have natural numbers
I tried formatting those cells to numbers etc. but nothing works. What is the mistake I’m making and how to fix it?
According to the comments, it seems that your problem is your data, you have likely strings in your cell, not numbers (maybe somehow imported?)
As already mentioned, changing the cell format doesn’t change the content of a cell, it just defines how to display data. The number
3.14 can be displayed as 3, as 3.140000, as 00003.14 or as 3.14E+00, nothing changes it’s value. However, a String
'3.14 is a combination of the characters
4 and has nothing to do with a number. Setting a cell format after the value is in the cell will not convert it to a number.
If you read your data into VBA, VBA will get the exact values from Excel and in your case, you will have to convert it into numbers manually, for example with the following routine. The
On Error Resume Next will prevent a type mismatch if a cell doesn’t contain something that can be converted into a number.
Sub ArrToNumber(ByRef arr) Dim i As Long, j As Long For i = LBound(arr, 1) To UBound(arr, 1) For j = LBound(arr, 2) To UBound(arr, 2) On Error Resume Next arr(i, j) = CDbl(arr(i, j)) On Error GoTo 0 Next Next End Sub
Now just add a call to this routine to your code. If you want to have the numbers also in Excel, remove the comment sign from the last statement.
Dim MachineCapacitySmallestArray() As Variant MachineCapacitySmallestArray = thisworkbook.worksheets(1).range("C25:D25") ArrToNumber MachineCapacitySmallestArray SmallestCapacity = Application.Min(MachineCapacitySmallestArray) ' thisworkbook.worksheets(1).range("C25:D25") = MachineCapacitySmallestArray
Answered By – FunThomas
Answer Checked By – David Goodson (AngularFixing Volunteer)