Increment Number by on unsorted list

  • Thread starter Thread starter willwonka
  • Start date Start date
W

willwonka

We have a task list log spreadsheet.

column is nothing but a sequential number. The problem is that we
sort this list all the time. What I want is that when we go to the
bottom of the list to add a task, I want it to figure out what the
next number is. The only caveat is that we started over from 0 was we
hit 1000. See below example

Column A
Task #
23
254
655
154
501
11
100
233
998
800

I would like to add some code that when I go to the cell underneath
800 in this example to pul lthe next number that is lower than say...
400.

I know the array formula to enter; but would rather do this with code
so that the uers can just hit cntrl T or some letter.

Here is the array formula that I would use.

{=MAX(IF(A1:A777<400,A1:A777))+1}

so in the example above, I would want A777 to be relative... one cell
above where cursor is.

I'm sure that this is a slam dunk for you pro VBAers.

thanks.
 
We have a task list log spreadsheet.

column is nothing but a sequential number. The problem is that we
sort this list all the time. What I want is that when we go to the
bottom of the list to add a task, I want it to figure out what the
next number is. The only caveat is that we started over from 0 was we
hit 1000. See below example

Column A
Task #
23
254
655
154
501
11
100
233
998
800

I would like to add some code that when I go to the cell underneath
800 in this example to pul lthe next number that is lower than say...
400.

I know the array formula to enter; but would rather do this with code
so that the uers can just hit cntrl T or some letter.

Here is the array formula that I would use.

{=MAX(IF(A1:A777<400,A1:A777))+1}

so in the example above, I would want A777 to be relative... one cell
above where cursor is.

I'm sure that this is a slam dunk for you pro VBAers.

thanks.
Click to expand...

Try this sub:

Sub next_number()
c = ActiveCell.Column
first_row = 1
last_row = ActiveCell.Row - 1
max_value = -1E+300
With ActiveSheet
For r = first_row To last_row
If .Cells(r, c) < 400 And .Cells(r, c) > max_value Then
max_value = .Cells(r, c)
End If
Next r
End With
ActiveCell.Value = max_value + 1
End Sub

Hope this helps / Lars-Åke
 
The -1E+300 is just a number that is lower than any of the numbers in
your column. If your numbers are all positive you can replace
max_value = -1E+300
with
max_value = 0

Lars-Åke
 
Back
Top