just learning
 

Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations
that I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

JP Ronse wrote:
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi

In the Rank function, Arg2 is required to be a Range variable, whereas the
other functions require a Arg2 as Double.

Regards,

"JP Ronse" skrev i meddelelsen
...
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Array start at zero when using worksheet functions in excel. See if these
changes help

Sub test_JP()
Dim arr(0 To 999) As Double
Dim i, v As Range

For i = 0 To 9
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub




"JP Ronse" wrote:

Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5 highlights 'arr'?



Wkr,

JP


"Dana DeLouis" wrote in message
...
For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations that
I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

JP Ronse wrote:
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

I'm not sure about the second error, but the first Rank example works when
I change it to the following:

Debug.Print Application.WorksheetFunction.Rank _
(Sheets("Sheet3").Range("B2"), v, 1)

just learning
 

Hi Joel,

Thank you very much for spending your time to help me out of this.

The issue is that the statement

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

does not allow to run the code.

See also the reply of Per.

Wkr,

JP


"Joel" wrote in message
...
Array start at zero when using worksheet functions in excel. See if these
changes help

Sub test_JP()
Dim arr(0 To 999) As Double
Dim i, v As Range

For i = 0 To 9
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub




"JP Ronse" wrote:

Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi Per,

You are right. I retyped the statement and saw that arg2 must be a range .

Thank you for your time. I should better read what the instructions are
saying instead wishful thinking.

Wkr,

JP


"Per Jessen" wrote in message
...
Hi

In the Rank function, Arg2 is required to be a Range variable, whereas the
other functions require a Arg2 as Double.

Regards,

"JP Ronse" skrev i meddelelsen
...
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi Arjen,

Tnx for your time.

The error comes from the statement

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Per Jessen explained that arg2 (arr) must be a range.



Wkr,

JP


"arjen van der wal" wrote in message
...
I'm not sure about the second error, but the first Rank example works when
I change it to the following:

Debug.Print Application.WorksheetFunction.Rank _
(Sheets("Sheet3").Range("B2"), v, 1)

just learning
 

If you look at the Intelli-help popup as you type the command in the code
window, it shows this for the syntax...

Rank(Arg1 As Double, Arg2 As Range, [Arg3] As Double)

I know the help for this function says the second argument can be an array
or a range, but it appears that VB imposes a "range only" requirement on
this second argument.

--
Rick (MVP - Excel)


"JP Ronse" wrote in message
...
Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5 highlights 'arr'?



Wkr,

JP


"Dana DeLouis" wrote in message
...
For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations
that I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

JP Ronse wrote:
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi Rick,

Tnx, Per Jessen said the same, and testing proved it.

Wkr,

JP



"Rick Rothstein" wrote in message
...
If you look at the Intelli-help popup as you type the command in the code
window, it shows this for the syntax...

Rank(Arg1 As Double, Arg2 As Range, [Arg3] As Double)

I know the help for this function says the second argument can be an array
or a range, but it appears that VB imposes a "range only" requirement on
this second argument.

--
Rick (MVP - Excel)


"JP Ronse" wrote in message
...
Hi Dana,

Tnx for your reply. The statement itself generates already the error
(compile error) and hitting <F5 highlights 'arr'?



Wkr,

JP


"Dana DeLouis" wrote in message
...
For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

Hi. In general, you are looking for the number 1 within this array to
find its rank. The number '1 is most likely not there.

Having said that, I too am getting errors. I've tried all variations
that I can think of with no luck. Hopefully, someone will jump in.

= = = = =
Dana DeLouis
(using Excel 2007)

JP Ronse wrote:
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub

just learning
 

Hi JP

Thanks for your reply. I am glad to help.

Sure, we can all benefit from the instructions/help which are provided.

Best regards,
Per

"JP Ronse" skrev i meddelelsen
...
Hi Per,

You are right. I retyped the statement and saw that arg2 must be a range .

Thank you for your time. I should better read what the instructions are
saying instead wishful thinking.

Wkr,

JP


"Per Jessen" wrote in message
...
Hi

In the Rank function, Arg2 is required to be a Range variable, whereas
the other functions require a Arg2 as Double.

Regards,

"JP Ronse" skrev i meddelelsen
...
Hi All,

Finding some interesting ways to use functions in VBA, I was trying ...

The last statement gives: Compile error: type mismatch.

Why is Large, Match working on an array and Rank not?

Wkr,

JP

Sub test_JP()
Dim arr(1 To 1000) As Double
Dim i, v As Range

For i = 1 To 10
arr(i) = Int((100 - 1 + 1) * Rnd + 1)
Next

Set v = Sheets("sheet3").Range("B1:B6")
'''
Debug.Print Application.WorksheetFunction.Large(arr, 1)
Debug.Print
Application.WorksheetFunction.Match(Application.Wo rksheetFunction.Large(arr,
1), arr, 0)
Debug.Print Application.WorksheetFunction.Rank(1, v, 0)
Debug.Print Application.WorksheetFunction.Rank(1, arr, 0)

End Sub