ExcelBanter - View Single Post

DG

Got it.

"DG" wrote in message
...
Actually I still have a problem.

I used your code (with the MaxOfArray function) and MaxVal returns 0

MaxVal = WorksheetFunction.Max(TheArray) ' returned 0
MaxOfArray = WorksheetFunction.Match(MaxVal, TheArray, 0) ' ERROR
Run-time error '1004' application-defined or object-defined error.

Right after MaxVal= .... I put in some code to see if my array/object was
being populated and it is:
MaxVal = WorksheetFunction.Max(TheArray)
x = MsgBox("Cell A5 - " & TheArray(4), vbOKOnly)
MaxOfArray = WorksheetFunction.Match(MaxVal, TheArray, 0)
And the MsgBox shows me what is in cell A5. So I know TheArray has data
but MaxVal is 0.

"DG" wrote in message
...
So, Technically I don't need MY function. I did not know there was a
WorksheetFucnctionMax or Match. That's is kind of what I tried first
Max(OrderDateArray), but of course it errored. So I tried to write my
own.

Thanks Joel.

"joel" wrote in message
...

There was two problems with your code

1) OrderDateArray is a range object and not an array. to find the
number of items in a range object use the property count

2) If sheet 2 wasn't selected then there was an error in the
following line

Set OrderDateArray = Sheets("Sheet2").Range("A2",
Range("A65536").End(xlUp))

A sheet reference is ommitted from this part of the statement :
Range("A65536"). When a sheet is left out vba uses the current sheet.
If the current sheet is not sheet 2 then the Range is refering to two
different sheets and an error occurs

3) You rindexing though a range wouldn't work. I used the worksheet
function Max and Match to get the relative position of the max value in
the range object.

Sub Forcast_Prod()
Dim OrderDateArray As Variant
With Sheets("sheet2")
Set OrderDateArray = .Range("A2", .Range("A65536").End(xlUp))
Max_Date = MaxOfArray(OrderDateArray)
End With
End Sub

Function MaxOfArray(ByRef TheArray As Variant) As Integer
' This function gives the max value of an integer array without
sorting
'the array
Dim i As Integer
Dim MaxIntIndex As Integer
MaxIntIndex = 0

MaxVal = WorksheetFunction.Max(TheArray)
MaxOfArray = WorksheetFunction.Match(MaxVal, TheArray, 0)

End Function

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=165679

Microsoft Office Help