ExcelBanter - Can someone explain why?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Can someone explain why? (https://www.excelbanter.com/excel-programming/427134-can-someone-explain-why.html)

Can someone explain why?

Hi,
Here is the situation:
I use EXCEL 2003

If I apply the following it does not work:
-------------------------------------------------------

Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

But this does with the ActiveSheet.Range
--------------------
Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = ActiveSheet.Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

P.S: The column is define as General

Thank's ahead

Can someone explain why?

Bobby,

Well it depends on your definition of 'Doesn't work'.

Both sub returns the maximum value in the range rngMax, what do you expect
them to do? What result are you getting?

Mike

"Bobby" wrote:

Hi,
Here is the situation:
I use EXCEL 2003

If I apply the following it does not work:
-------------------------------------------------------

Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

But this does with the ActiveSheet.Range
--------------------
Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = ActiveSheet.Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

P.S: The column is define as General

Thank's ahead

Can someone explain why?

On Apr 18, 8:28*am, "Don Guillett" wrote:
I tested yours and it gave me the max. What does "it does not work" mean?

*Why not just use.

Sub maxinothersheet()
*MsgBox Application.Max(Sheets("a b").Range("e2:e22"))
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Bobby" wrote in message

...

Hi,
Here is the situation:
I use EXCEL 2003

If I apply the following it does not work:
-------------------------------------------------------

Sub test()
* *Dim maxnumber As Integer, rngMax As Range

* *Sheets("Planif-max52").Select
* *Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
* *maxnumber = Application.WorksheetFunction.Max(rngMax)
* * MsgBox maxnumber

End Sub

But this does with the ActiveSheet.Range
--------------------
Sub test()
* *Dim maxnumber As Integer, rngMax As Range

* *Sheets("Planif-max52").Select
* *Set rngMax = ActiveSheet.Range("B2:B53")
* *maxnumber = Application.WorksheetFunction.Max(rngMax)
* * MsgBox maxnumber

End Sub

P.S: The column is define as General

Thank's ahead- Hide quoted text -

- Show quoted text -

Ok,
I meet that in the first example I get a zero value even if I have 500
for qty in the range. But if I use the ActiveSheet.Range I get the
500 value. Saying that if it works with you then I must be doing
something wrong!

Can someone explain why?

"Bobby" wrote:
If I apply the following it does not work:
[....]
Sheets("Planif-max52").Select
Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)

In this case, you are looking at B2:B53 in worksheet Rap-ECL.

But this does with the ActiveSheet.Range
[....]
Sheets("Planif-max52").Select
Set rngMax = ActiveSheet.Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)

In this case, you are looking at B2:B53 in worksheet Planif-max52.

In a later posting, "Bobby" wrote:
in the first example I get a zero value even
if I have 500 for qty in the range. But if I use the
ActiveSheet.Range I get the 500 value.

When you say "the 500 value", I get the impression you think you are looking
at the same B2:B53 in both implementations. That is not the case. Could
that explain your problem?

In any case, in Excel, go to both worksheets, and enter =MAX(B2:B53) in a
cell to confirm your expectations.

----- original posting -----

"Bobby" wrote in message
...
Hi,
Here is the situation:
I use EXCEL 2003

If I apply the following it does not work:
-------------------------------------------------------

Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

But this does with the ActiveSheet.Range
--------------------
Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = ActiveSheet.Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

P.S: The column is define as General

Thank's ahead

Can someone explain why?

Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")

Do you have values int B2:B53 of this worksheet? If it is returning zero
then it is checking something. If your sheet name was misspelled you would
get an error message. The code looks OK.

"Bobby" wrote:

Hi,
Here is the situation:
I use EXCEL 2003

If I apply the following it does not work:
-------------------------------------------------------

Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = Worksheets("Rap-ELC").Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

But this does with the ActiveSheet.Range
--------------------
Sub test()
Dim maxnumber As Integer, rngMax As Range

Sheets("Planif-max52").Select
Set rngMax = ActiveSheet.Range("B2:B53")
maxnumber = Application.WorksheetFunction.Max(rngMax)
MsgBox maxnumber

End Sub

P.S: The column is define as General

Thank's ahead