Application.Max wierdness
 

I just discovered something weird. I hope I can explain it clearly.
I have a Worksheet_Change event that, among other things, incrememts the
last check number in column J:
Range("I3").End(xlDown)(1, 2) = Application.Max(Range("J:J")) + 1

Occasionally I have reason to manually type in a check number in that
column. If I do, the next time I trigger the Worksheet_Change event, the
check number is NOT incremeted.

Can anyone explain this, and hopefully point me to a fix?

--
David

Application.Max wierdness
 

Without seeing your code, it is a little difficult. I knocked up a simple
test and it worked.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"David" wrote in message
...
I just discovered something weird. I hope I can explain it clearly.
I have a Worksheet_Change event that, among other things, incrememts the
last check number in column J:
Range("I3").End(xlDown)(1, 2) = Application.Max(Range("J:J")) + 1

Occasionally I have reason to manually type in a check number in that
column. If I do, the next time I trigger the Worksheet_Change event, the
check number is NOT incremeted.

Can anyone explain this, and hopefully point me to a fix?

--
David

Application.Max wierdness
 

Bob Phillips wrote

Without seeing your code, it is a little difficult. I knocked up a simple
test and it worked.

Thanks for responding.

Well, I've worked it out. Recently, because I occasionally enter --- to
indicate no check number for things like deposits, I formatted the column
as Text to eliminate the apostrophe and that affected manually typed in
check numbers that 'threw' the Worksheet_Change event. Formatting back to
General fixed things. Can't have my cake and eat it, too <sigh.

--
David

Application.Max wierdness
 

You are probably better off leaving the column formatted for General,
but you can have your cake and eat it to. To force XL to do a type
conversion and then get the largest value in the column, use

evaluate("Max(0+" & range(range("a1"),cells(rows.Count,1).end
(xlup)).Address & ")")

Do note that the Evaluate function has its own limitations.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , dturner4_1999
@yahoo.com says...
Bob Phillips wrote

Without seeing your code, it is a little difficult. I knocked up a simple
test and it worked.

Thanks for responding.

Well, I've worked it out. Recently, because I occasionally enter --- to
indicate no check number for things like deposits, I formatted the column
as Text to eliminate the apostrophe and that affected manually typed in
check numbers that 'threw' the Worksheet_Change event. Formatting back to
General fixed things. Can't have my cake and eat it, too <sigh.

Application.Max wierdness
 

Would value 0 in cells that don't need checking suffice. If so another way
to enjoy the odd bit of cake:

Create a Custom number format like this

General;General;"- - -"

positive format ; negative format ; zero format

Enter 0 instead of dashes to indicate no check.

Regards,
Peter T


Well, I've worked it out. Recently, because I occasionally enter --- to
indicate no check number for things like deposits, I formatted the column
as Text to eliminate the apostrophe and that affected manually typed in
check numbers that 'threw' the Worksheet_Change event. Formatting back to
General fixed things. Can't have my cake and eat it, too <sigh.

--
David

Application.Max wierdness
 

Tushar Mehta wrote


You are probably better off leaving the column formatted for General,
but you can have your cake and eat it to. To force XL to do a type
conversion and then get the largest value in the column, use

evaluate("Max(0+" & range(range("a1"),cells(rows.Count,1).end
(xlup)).Address & ")")


Hmm... Elegant. However, after subbing "j1" for "a1" to match my check#
column, substituted your Evaluate formula for existing offset reference,
reformatted the column as Text, manually typed in a check # then ran the
macro for next check and no increment :(

Your offering does work if I don't manually enter a check# before running
the macro.

Further observation: If I highlight two check#'s above my target and
autofill down one instead of manually typing the check#, then run the
macro, things work no matter what the format.

I'm obviously missing something. Lacking further clarification, I think
I'll leave as General and go with Peter's Custom format and enjoy my cake.

--
David

Application.Max wierdness
 

Peter T wrote

Would value 0 in cells that don't need checking suffice. If so another
way to enjoy the odd bit of cake:

Create a Custom number format like this

General;General;"- - -"

positive format ; negative format ; zero format

Enter 0 instead of dashes to indicate no check.

That'll work --- thanks.

--
David

Application.Max wierdness
 

In article , dturner4_1999
@yahoo.com says...
Tushar Mehta wrote

evaluate("Max(0+" & range(range("a1"),cells(rows.Count,1).end
(xlup)).Address & ")")


Hmm... Elegant. However, after subbing "j1" for "a1" to match my check#
column, substituted your Evaluate formula for existing offset reference,
reformatted the column as Text, manually typed in a check # then ran the
macro for next check and no increment :(

Did you also change the cells(rows.count,1) to cells(rows.count,10)?
Or to the more transparent cells(rows.count,columns("J").column).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , dturner4_1999
@yahoo.com says...
Tushar Mehta wrote


You are probably better off leaving the column formatted for General,
but you can have your cake and eat it to. To force XL to do a type
conversion and then get the largest value in the column, use

evaluate("Max(0+" & range(range("a1"),cells(rows.Count,1).end
(xlup)).Address & ")")


Hmm... Elegant. However, after subbing "j1" for "a1" to match my check#
column, substituted your Evaluate formula for existing offset reference,
reformatted the column as Text, manually typed in a check # then ran the
macro for next check and no increment :(

Your offering does work if I don't manually enter a check# before running
the macro.

Further observation: If I highlight two check#'s above my target and
autofill down one instead of manually typing the check#, then run the
macro, things work no matter what the format.

I'm obviously missing something. Lacking further clarification, I think
I'll leave as General and go with Peter's Custom format and enjoy my cake.

Application.Max wierdness
 

"Tushar Mehta" wrote in message
om...

Did you also change the cells(rows.count,1) to cells(rows.count,10)?
Or to the more transparent cells(rows.count,columns("J").column).

or the even more transparent

cells(rows.count,"J")

Application.Max wierdness
 

Tushar Mehta wrote

Did you also change the cells(rows.count,1) to cells(rows.count,10)?
Or to the more transparent cells(rows.count,columns("J").column)

Nope, and furthermore, I forgot I had another line that applied the same
Appication.Max test, so here's my new line (same syntax in both places):

Range("I3").End(xlDown)(1, 2) = _
Evaluate("Max(0+" & Range(Range("J1"), _
Cells(Rows.Count, "J").End(xlUp)).Address & ")")

Unfortunately, results in #VALUE!

--
David