Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not sure how to explain.... | Excel Discussion (Misc queries) | |||
Please explain | Excel Worksheet Functions | |||
How to explain??... | Excel Discussion (Misc queries) | |||
Let me see if I can explain this... | Excel Worksheet Functions | |||
Can someone explain to me? Pls? | Excel Programming |