Can someone explain why?

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

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
 
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
 
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
 
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














- Show quoted text -
Click to expand...

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!
 
Bobby said:
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)
Click to expand...

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)
Click to expand...

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


Bobby said:
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.
Click to expand...

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 -----
 
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.
 
Back
Top