Find and add one Macro

I

Izzy

Hello,

I'm looking for a macro that will find the largest number in a column of
numbers and automatically + 1 and insert that number in the cell of the
column anytime new row is inserted or row of data is added to the column.
Anyone ever seen anything like that?

Example: Column downward has 1, 2, 3, 4. I insert a row between 2 and 3 and
type some data in another cell of that row. I want the cell of the row and
column to automatically show 5 so the column would read downward 1, 2, 5, 3,
4. Anyone ever seen anything like this?

Thanks.

Izzy
 
B

Bob Greenblatt

Hello,

I'm looking for a macro that will find the largest number in a column of
numbers and automatically + 1 and insert that number in the cell of the
column anytime new row is inserted or row of data is added to the column.
Anyone ever seen anything like that?

Example: Column downward has 1, 2, 3, 4. I insert a row between 2 and 3 and
type some data in another cell of that row. I want the cell of the row and
column to automatically show 5 so the column would read downward 1, 2, 5, 3,
4. Anyone ever seen anything like this?

Thanks.

Izzy
Click to expand...
Here's a "quickie" that will insert the next largest number of all numbers
in column A into a blank cell in column A. Paste this macro into the
worksheet's code page. (The blank cell in A will not be filled in until a
change is made elsewhere in the sheet.)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lMax As Long
Dim vmax As Variant
lMax = UsedRange.Rows.Count
vmax = Application.Max(Range("a1:a" & lMax))
If Len(Range("a" & Target.Row)) = 0 Then Range("a" & Target.Row) = vmax
+ 1
End Sub


Watch out for line wrap.
 
I

Izzy

I think I'm trying to insert this macro all wrong. I'm going into VB and
trying to paste and save as a macro. This is probably a stupid question, but
how do I get to the code page.
 
B

Bob Greenblatt

I think I'm trying to insert this macro all wrong. I'm going into VB and
trying to paste and save as a macro. This is probably a stupid question, but
how do I get to the code page.
Click to expand...

When in the VB environment, double click on the Excel sheet from the project
explorer where you want the macro. Then paste the code into that page.
 
I

Izzy

I knew it was a stupid question when I posted it. I right clicked the
worksheet and selected "View Code".

The code you posted works exactly how you described it. I appreciate your
help. Thanks!

Izzy
 
Top