Type Mismatch with UBound and arrays

[DG]

I can't see what is wrong with this code

In the Function MaxOfArray I get a type mismatch on the line "For i = 1 To
UBound(TheArray). I think it has something to do with Set OrderDateArray =
Sheets("Sheet2").Range("A2", Range("A65536").End(xlUp)) , which has only 36
rows.

Even if I put k = UBound(OrderDateArray) in the main sub I get the error. I
know this must be something simple that I can't see.

Sub Forcast_Prod()
Dim OrderDateArray As Variant
Set OrderDateArray = Sheets("Sheet2").Range("A2",
Range("A65536").End(xlUp))
Max_Date = MaxOfArray(OrderDateArray)
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

For i = 1 To UBound(TheArray)
If TheArray(i) > TheArray(MaxIntIndex) Then
MaxIntIndex = i
End If
Next

'index of max value is MaxValOfArray
MaxOfArray = TheArray(MaxIntIndex)
End Function

 

[joel]

There was two problems with your code

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

2) If sheet 2 wasn't selected then there was an error in th
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 tw
different sheets and an error occurs

3) You rindexing though a range wouldn't work. I used the workshee
function Max and Match to get the relative position of the max value i
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 withou
sorting
'the array
Dim i As Integer
Dim MaxIntIndex As Integer
MaxIntIndex = 0

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


End Functio

 

[Dave Peterson]

If you wanted to pass an array to that function (not a range), then you'd have
to pick up the values from that range.

This will create a 1 row by x column (multidemension array!):

Option Explicit
Sub Forcast_Prod()
Dim OrderDateArray As Variant
Dim Max_Date As Long 'are you sure????
With Worksheets("sheet2")
OrderDateArray = .Range("A2", _
.Cells(.Rows.Count, "A").End(xlUp)).Value
End With
Max_Date = MaxOfArray(OrderDateArray)
End Sub

Notice that I used the number of rows in the worksheet. The code won't have to
change if you start using xl2007+ (with 1 million rows).

And that the Set statement is gone. With the set statement, that OrderDateArray
becomes a range (since variants can hold anything).

Then to look for the max, you can use:
Function MaxOfArray(ByRef TheArray As Variant) As Long
' This function gives the max value of an integer array
' without sorting the array

Dim i As Long
Dim MaxIntIndex As Long

MaxIntIndex = 1

For i = LBound(TheArray, 1) To UBound(TheArray, 1)
If TheArray(i, 1) > TheArray(MaxIntIndex, 1) Then
MaxIntIndex = i
End If
Next i

'index of max value is MaxValOfArray
MaxOfArray = TheArray(MaxIntIndex, 1)

End Function

Notice I'm only looking at the first column of the array. And I've used Longs
instead of Integers. Longs turn out to be quicker and I don't have to worry
about exceeding a limit (32k???).

But you could drop the function and just trust excel itself:

Option Explicit
Sub Forcast_Prod()
Dim myRng As Range
Dim Max_Date As Long
With Worksheets("sheet2")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
Max_Date = Application.Max(myRng)
MsgBox Max_Date
End Sub

Notice that the Set is back and the .value is gone--and I used a range variable
(not variant).

 

[Ryan H]

What is TheArray? Are you missing some code in your post. I don't see
TheArray set to anything in your code. TheArray seems to be an undelared
variable. Declare the variable then set it equal to something.

I would highly recommend you use Option Explicit at the very top of your
module to prevent any undeclared variables from being used which can cause
errors like yours.

Hope this helps! If so, let me know, click "YES" below

 

[Dave Peterson]

This is a 1 row by 1 column array, but still two dimensions.

It's the equivalent of something like:
dim myArr(1 to 10, 1 to 1) as long

 

[DG]

Thanks Dave,

This is a 1 row by 1 column array, but still two dimensions.

It's the equivalent of something like:
dim myArr(1 to 10, 1 to 1) as long

 

[DG]

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.

 

[DG]

Actually I still have a problem.

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

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.

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.

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/showthread.php?t=165679

Microsoft Office Help

 

[DG]

Got it.

Actually I still have a problem.

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

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.

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.

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/showthread.php?t=165679

Microsoft Office Help