Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with UBound and arrays
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch passing arrays via COM | Excel Programming | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Help - Type mismatch when running loop with strings from arrays | Excel Programming |