Determine last number in a column that contains numbers, and strings

[anakaine]

All,

I'm trying to figure out how to determine the last number in a column, with a caveat. The column will contain both integers (ascending), and strings (also ascending, but the format is variable, and not required for this exercise).

What I would like to do, is have a button on a userform that when clicked looks at the column, finds the last number used and increments it by 1. There may be white space between the current cell and the number, there may also be a string in the way. Example attached

Many thanks for the help!


sample_help.xlsx

Bonus points: Looking at the example file, is there a simple routine I could use to increment the string? The data will virtually always have the same format, with 2 incrementing characters at the end. In extremely rare cases it might extend to 3 characters. If not found, create first example + 01 at the end


I've done pretty well with my vba so far, but this one has me stumped :/

[Ed_Collins]

This takes your code, with the data in column G (the 7th column) and increments the last numeric number if finds by 1. (It reports the new number in a message box.)

Change the 7 to the appropriate column, as needed.

max_rows = Sheet1.Cells(Rows.Count, 7).End(xlUp).Row

For Row = 1 To max_rows
  If IsNumeric(Cells(Row, 7)) Then
      Number = Cells(Row, 7) + 1
  End If
Next
MsgBox (Number)

[anakaine]

Thank you very much!
Just so I've got it in my head whats happening here:

variable max_rows = the total number of lines in column 7 with data in them

Then we run a loop from the first row until the last, looking for numeric numbers.
Every time a numeric number is found in a row +1 is added to it, replacing whatever the last +1 was in 'Number'.



Works very well, thanks!

[Ed_Collins]

Actually, now that I think about it, why not just start at the bottom and work our way up? After the first numeric number we find, we can then break out of the loop immediately. Probably more elegant and certainly a microsecond faster.

For row = max_rows to 1 step -1 would be the code. Once you find a numeric field, then EXIT FOR.

[anakaine]

Actually, now that I think about it, why not just start at the bottom and work our way up? After the first numeric number we find, we can then break out of the loop immediately. Probably more elegant and certainly a microsecond faster.

For row = max_rows to 1 step -1 would be the code. Once you find a numeric field, then EXIT FOR.

Thank you again! I'll have a go at implementing this today and see how it goes

[Ed_Collins]

Bingo. That's exactly what the code is doing.

There are probably several other ways of doing the same thing. That was the fastest for me, and the first thing that came to my mind.

[Trebor76]

Hi anakaine,

You can do this via a simple native formula like so:

=MAX(G2:G22)+1

If you want a VBA solution just adapt the formula into the required cell location i.e.

Option Explicit
Sub Macro2()

    Dim lngMyRow As Long

    lngMyRow = Cells(Rows.Count, "G").End(xlUp).Row 'Find the last row in Col. G
    
    'Puts the MAX formula two rows beneath the last row found from above
    With Range("G" & lngMyRow + 3)
        .Formula = "=MAX(G2:G" & lngMyRow & ")+1"
        .Value = .Value 'Converts the formula to a value. Comment out or delete this if the original formual is required.
    End With

End Sub

HTH

Robert

[skywriter]

Here's a non looping solution.

Sub NextNumber()

Dim NextNumber As Long

NextNumber = WorksheetFunction.Max(Worksheets("Sheet1").Range("G:G")) + 1

MsgBox NextNumber

End Sub

[Trebor76]

Here's a non looping solution

Not sure if you posted while I was but mine isn't a looping solution.

[skywriter]

Not sure if you posted while I was but mine isn't a looping solution.

I was reading the previous posts, thought I had another solution, proceeded to write my solution and posted it.

When the screen refreshed your post was already there.

I should have refreshed the screen first, it happens.

[apo]

Using Trebor76's formula..

Sub Test()
  Range("G" & Rows.Count).End(xlUp).Offset(3).Value = [=MAX(G:G)+1]
End Sub

[Trebor76]

I should have refreshed the screen first, it happens.

Of course - I thought that may have been the case. Just wanted to make sure in case others were reading over our replies. Thanks.

Using Trebor76's formula

Nice apo

Robert

[anakaine]

Thanks to all others that have replied

I've opted with one of the first two solutions, as working with example data it became clear that the MAX function may not be entirely appropriate for our data set. Some isolated examples where more than one range of samples were used in a single file became apparent. On this instance if the first lot of predefined sample numbers was larger than the last lot (most recently taken) MAX would start working with the first lot again, rather than the most recent

[Trebor76]

MAX would start working with the first lot again, rather than the most recent

Not sure what you mean

The following will return the maximum number from A1:A3 across Sheet1 and Sheet2:

=MAX(Sheet1!A1:A3,Sheet2!A1:A3)

If you are nonetheless happy that your problem has been solved, if you could mark the thread as such it would be appreciated.

Kind regards,

Robert

[anakaine]

Not sure what you mean

The following will return the maximum number from A1:A3 across Sheet1 and Sheet2:

=MAX(Sheet1!A1:A3,Sheet2!A1:A3)

If you are nonetheless happy that your problem has been solved, if you could mark the thread as such it would be appreciated.

Kind regards,

Robert

Robert,

In testin the various bits of code I found that in some instances we had source data that was derived from non sequenced sample numbers. Ie two different sample sets comprised the total, and rarely one sample set was numerically higher than the latter. The max function would not have allowed the incrementing of the last seen sample number, but rather would have added to the highest number in the column.

Eg

Samples:
5001
5002
5003
1007
1008
1009

The newst sample set used (again, rare case, unforeseen) was the 1000 series, but max would have looked at the 5000 series and added 1.

I wasn't aware of this issue before posting the question, but discovered it because of the variety of answers, so thank you anyway!

[Trebor76]

So in your above example, how do you get Ed's VBA method to return 1010? Just curious.

[jindon]

Try

Sub test()
    With Columns("g").SpecialCells(2, 1)
        With .Areas(.Areas.Count)
            Range("g" & Rows.Count).End(xlUp)(4).Value = .Cells(.Count) + 1
        End With
    End With
End Sub