Finding Min and Max Values from Array

[Stighre]

I have been trying to set up a macro to find the Minimum and Maximum values from an array of temperatures for painting... so far, partially successful.

The problems I am having are that the values have 1 decimal place and can be anywhere between 22.7 to -1.4. When they come they are put on the spreadsheet, the MinTemp can be 10.0 and MaxTemp 9.9, totally reversed.


Dim Info(2000, 2) As String
Dim MonthValue(31, 9) As Variant
Dim Working1 As Date
Dim I As Integer
Dim J As Integer
Dim Tot As Integer

For I = 1 To 31
MonthValue(I, 2) = 99
Next I

For I = 1 To 2000
Info(I, 1) = Range("B" & I + 1)
Next I

Tot = 1
For I = 1 To 2000
Working1 = Info(I, 1)
If Month(Working1) = Month(DateValue) Then
If Day(Info(I, 1)) > Tot Then Tot = Tot + 1
If Day(Info(I, 1)) = Tot Then
If Info(I, 2) > MonthValue(Tot, 1) Then MaxTemp = Info(I, 2)
If Info(I, 2) < MonthValue(Tot, 2) Then MinTemp = Info(I, 2)
End If
End If
Next I


Range("A3") = MaxTemp
Range("A4") = MinTemp


Any help would be greatly appriciated.

 

[KMacauley]

Hi Stighre,

Are you having an issue? If so, can you provide some sample data in a dropbox so that I can have a better understanding of the issue.

Kim.

 

[mikerickson]

The data in Info are strings and that causes strange things to happen to numbers stored as strings.
To find the minimum value of the second column of Info, you could dim it as Variant (or Double if there are only date or numeric entries) and then use some worksheet functions.

Dim Info (1 To 2000, 1 To 2) As Variant
'...
maxValue = Application.Max(Application.Index(Info, 0, 2))
minValue = Application.Min(Application.Index(Info, 0, 2))