Solved: Application.Max(rng)

[Bob Blooms]

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]

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]

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]

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]

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.