Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
Greetings,
I have an invoice number generator on a UserForm run from a CommandButton. Here is the code: Dim rIN1 As Range Dim rIN2 As Range ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1.Value = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 With the named range "rInvNums_1st" equal to 3311000 (The first possible invoice number minus 1) and the named range "rInvNums_List" equal to the numbers in column C, (the list of existing invoice numbers). This should produce the number 3311001 for the first invoice number, instead it is producing the number 1! When I check it with debug, debug shows the value of rIN1 as 3311000 and did not show anything for rIN2. Anyone have any ideas as to what is wrong with this code? Any help is greatly appreciated. Thanks. -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
I forgot to mention, Column C is at this time completely empty.
-Minitman On Fri, 09 Nov 2007 01:05:17 -0600, Minitman wrote: Greetings, I have an invoice number generator on a UserForm run from a CommandButton. Here is the code: Dim rIN1 As Range Dim rIN2 As Range ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1.Value = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 With the named range "rInvNums_1st" equal to 3311000 (The first possible invoice number minus 1) and the named range "rInvNums_List" equal to the numbers in column C, (the list of existing invoice numbers). This should produce the number 3311001 for the first invoice number, instead it is producing the number 1! When I check it with debug, debug shows the value of rIN1 as 3311000 and did not show anything for rIN2. Anyone have any ideas as to what is wrong with this code? Any help is greatly appreciated. Thanks. -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
i entered 3311000 in D1 and named it rInvNums_1st
i named a range in column C1:C46 , C rInvNums_List . it is empty i run this code and i get 3311001 as the result in the immediate window Sub test() Dim rIN1 As Range Dim rIN2 As Range Dim Input_1 As Long ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1 = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 Debug.Print Input_1 End Sub -- Gary "Minitman" wrote in message ... I forgot to mention, Column C is at this time completely empty. -Minitman On Fri, 09 Nov 2007 01:05:17 -0600, Minitman wrote: Greetings, I have an invoice number generator on a UserForm run from a CommandButton. Here is the code: Dim rIN1 As Range Dim rIN2 As Range ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1.Value = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 With the named range "rInvNums_1st" equal to 3311000 (The first possible invoice number minus 1) and the named range "rInvNums_List" equal to the numbers in column C, (the list of existing invoice numbers). This should produce the number 3311001 for the first invoice number, instead it is producing the number 1! When I check it with debug, debug shows the value of rIN1 as 3311000 and did not show anything for rIN2. Anyone have any ideas as to what is wrong with this code? Any help is greatly appreciated. Thanks. -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
Might you have text in one of your ranges?
-- HTH, Barb Reinhardt "Minitman" wrote: I forgot to mention, Column C is at this time completely empty. -Minitman On Fri, 09 Nov 2007 01:05:17 -0600, Minitman wrote: Greetings, I have an invoice number generator on a UserForm run from a CommandButton. Here is the code: Dim rIN1 As Range Dim rIN2 As Range ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1.Value = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 With the named range "rInvNums_1st" equal to 3311000 (The first possible invoice number minus 1) and the named range "rInvNums_List" equal to the numbers in column C, (the list of existing invoice numbers). This should produce the number 3311001 for the first invoice number, instead it is producing the number 1! When I check it with debug, debug shows the value of rIN1 as 3311000 and did not show anything for rIN2. Anyone have any ideas as to what is wrong with this code? Any help is greatly appreciated. Thanks. -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
Hey Gary,
Thanks for the reply. As you noted, there is nothing wrong with the code. There was however something wrong with one of the named ranges - rInvNums_1st. This is the formula in the cell called rInvNums_1st: =(YEAR(WBDate)-1974)&TEXT(MONTH(WBDate),"00")&TEXT(0,"000") Which returned the number 3311000 for the workbook called 2007-11.xls, this seemed to be working fine - but it wasn't!!! And that is what was confusing me! I needed to take the Value of this formula and pass that on like so: =VALUE((YEAR(WBDate)-1974)&TEXT(MONTH(WBDate),"00")&TEXT(0,"000")) Now it works. Your confirmation that the code was working in the UserForm made me look on the sheet at the named ranges. It was when I tried to get MAX() to work in a cell and it was doing the same thing as the UserForm, that I took a second look at the range rInvNums_1st. It looked like it had the right result showing in the cell called rInvNums_1st, but I could not get the MAX() formula to see it when I used both ranges. So I tried the value command and it cleared up all of the problems -Minitman On Fri, 9 Nov 2007 02:25:21 -0500, "Gary Keramidas" <GKeramidasATmsn.com wrote: i entered 3311000 in D1 and named it rInvNums_1st i named a range in column C1:C46 , C rInvNums_List . it is empty i run this code and i get 3311001 as the result in the immediate window Sub test() Dim rIN1 As Range Dim rIN2 As Range Dim Input_1 As Long ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1 = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 Debug.Print Input_1 End Sub -- Gary "Minitman" wrote in message .. . I forgot to mention, Column C is at this time completely empty. -Minitman On Fri, 09 Nov 2007 01:05:17 -0600, Minitman wrote: Greetings, I have an invoice number generator on a UserForm run from a CommandButton. Here is the code: Dim rIN1 As Range Dim rIN2 As Range ThisWorkbook.Activate Set rIN1 = Range("rInvNums_1st") Set rIN2 = Range("rInvNums_List") Input_1.Value = Application.WorksheetFunction.Max(rIN1, rIN2) + 1 With the named range "rInvNums_1st" equal to 3311000 (The first possible invoice number minus 1) and the named range "rInvNums_List" equal to the numbers in column C, (the list of existing invoice numbers). This should produce the number 3311001 for the first invoice number, instead it is producing the number 1! When I check it with debug, debug shows the value of rIN1 as 3311000 and did not show anything for rIN2. Anyone have any ideas as to what is wrong with this code? Any help is greatly appreciated. Thanks. -Minitman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aplication.Worksheetfunction.MAX() Stopped Working
Hey Barb,
Thanks for the reply. A good question. however, the MAX() function doesn't care if there is anything other then numbers, it simply ignores them. Or so says the Microsoft help file. I found the problem and explained it in detail in my reply to Gary which I just posted. I needed the value of a formula that was in the first named range. A good place to start looking next time. <G -Minitman On Fri, 9 Nov 2007 05:39:01 -0800, Barb Reinhardt wrote: Might you have text in one of your ranges? -- HTH, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction not working correctly | Excel Discussion (Misc queries) | |||
Add-in stopped working | Excel Programming | |||
VB Stopped Working | Excel Worksheet Functions | |||
How do I open an aplication and run a file in that aplication use. | Excel Programming | |||
Tab stopped working | Excel Programming |