View Full Version : Solved: Application.Max(rng)
Bob Blooms
12-14-2009, 12:57 PM
Don't know if the title is appropriate to my issue, but here it is. I want to automatically assign a ID number to each new added record. I tried myMax = Application.Max(rng) without success. I can use to code that reads a cell on the active worksheet that has =max(w2:w1000), but it is problematic as it limits the number of lines and can be deleted. Any thoughts? Please! :dunno
lucas
12-14-2009, 01:32 PM
Would a formula work and what do the id numbers look like?
see attached for an idea that might work for you. Formula's are in column A
Bob Blooms
12-14-2009, 02:00 PM
Thanks for the quick replyl. The ID numbers are simple integers. I am hoping to add the ID number via VBA code. I looked at the workbook and have concerns with formulae being mixed with hard data.
mbarron
12-14-2009, 02:19 PM
How about this one. It inserts the next highest number, based on the A column, into the first available cell (at the bottom of the column) in the A column
Sub newID()
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _
Application.WorksheetFunction.Max(Range("A:A")) + 1
End Sub
Or this one. It inserts the next number into the selected cell based on the highest number in the A column
Sub IDintoSelection()
Selection = Application.WorksheetFunction.Max(Range("a:A")) + 1
End Sub
Bob Blooms
12-15-2009, 08:03 AM
This is what I ended up with. What was messing me up was the lact of "WorksheetFunction" in the code
Dim intIDno As Integer
Dim ID As Range
intIDno = Application.WorksheetFunction.Max(Range("ID"))
c.Offset(0, 22).Value = intIDno + 1
Works like a charm. Thanks!. Mark this issue solved.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.