Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > just learning
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 690
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default 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)
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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




Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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)


Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 174
Default 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





Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Learning VB Paige Excel Programming 3 October 28th 08 05:57 PM
Learning VBA Neal Excel Programming 2 May 25th 06 06:45 PM
Learning VBA [email protected] Excel Programming 3 March 9th 06 04:42 AM
Just learning VBA.. Deb Lang Excel Programming 1 October 22nd 03 11:46 PM
Learning VBA Randy Harris Excel Programming 2 September 6th 03 10:59 PM


All times are GMT +1. The time now is 02:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement