Aplication.Worksheetfunction.MAX() Stopped Working

[Minitman]

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

 

[Minitman]

I forgot to mention, Column C is at this time completely empty.

-Minitman

 

[Gary Keramidas]

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

 

[Guest]

Might you have text in one of your ranges?

 

[Minitman]

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

 

[Minitman]

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