Finding MAX Value of Part of Cell Contents
 

I have a list of values in :
Sheet8.Range("E:E")
that are formatted as:
ST 1234 ' IE "ST_1234" where "_" = Space

I want to place the Maximum Numerical Value +1 into a Textbox on a Userform,
But to date i have yet worked out how to manage it?

Any One assist me ?
Corey....

Finding MAX Value of Part of Cell Contents
 

Corey:

This function will find the value for you. You then have to add one to it
and put it in the text box.

Function findmax() As Long
Const iColumn As Integer = 5 ' column to use
Const iCharsToIgnore As Integer = 3 ' chars to ignore at start
Dim ws As Worksheet
Dim lRowStart As Long, lRowEnd As Long
Dim rCell As Range
Dim lMax As Long ' from spec no -ve values and only integers lmax
default 0 ok
Dim lCur As Long ' current number
' set sheet to use
Set ws = ActiveSheet

'find first and last rows
lRowStart = ws.UsedRange.Row
lRowEnd = ws.UsedRange.Rows.Count + lRowStart - 1
On Error Resume Next ' catch errors
For Each rCell In Range(Cells(lRowStart, iColumn), Cells(lRowEnd,
iColumn))
If rCell < "" Then
' on error uses last value ok may want better error checking
lCur = Right(rCell.Value, Len(rCell.Value) - iCharsToIgnore)
If lCur lMax Then lMax = lCur
End If
Next rCell
findmax = lMax
End Function

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Corey" wrote:

I have a list of values in :
Sheet8.Range("E:E")
that are formatted as:
ST 1234 ' IE "ST_1234" where "_" = Space

I want to place the Maximum Numerical Value +1 into a Textbox on a Userform,
But to date i have yet worked out how to manage it?

Any One assist me ?
Corey....

Finding MAX Value of Part of Cell Contents
 

Thank you for the code Martin,
It works great, but i then discovered i need to use it across more than just
1 Column.

Is there a simply way to have it do the following:

Function findmax() As Long
Const iColumn As Integer = 5 '<================= Look in Columns 5 & 7 &
9 & 11 INSTEAD of just 5???
Const iCharsToIgnore As Integer = 3 ' chars to ignore at start
Dim ws As Worksheet
Dim lRowStart As Long, lRowEnd As Long
Dim rCell As Range
Dim lMax As Long ' from spec no -ve values and only integers lmax
default 0 ok
Dim lCur As Long ' current number
Sheet8.Select ' set sheet to use
Set ws = ActiveSheet
'find first and last rows
lRowStart = ws.UsedRange.Row
lRowEnd = ws.UsedRange.Rows.Count + lRowStart - 1
On Error Resume Next ' catch errors
For Each rCell In Range(Cells(lRowStart, iColumn), Cells(lRowEnd,
iColumn))
If rCell < "" Then
' on error uses last value ok may want better error checking
lCur = Right(rCell.Value, Len(rCell.Value) - iCharsToIgnore)
If lCur lMax Then lMax = lCur
End If
Next rCell
findmax = lMax
UserForm5.TextBox7.Value = "ST " & lMax + 1
End Function




Corey....
"Martin Fishlock" wrote in message
...
Corey:

This function will find the value for you. You then have to add one to it
and put it in the text box.

Function findmax() As Long
Const iColumn As Integer = 5 ' column to use
Const iCharsToIgnore As Integer = 3 ' chars to ignore at start
Dim ws As Worksheet
Dim lRowStart As Long, lRowEnd As Long
Dim rCell As Range
Dim lMax As Long ' from spec no -ve values and only integers lmax
default 0 ok
Dim lCur As Long ' current number
' set sheet to use
Set ws = ActiveSheet

'find first and last rows
lRowStart = ws.UsedRange.Row
lRowEnd = ws.UsedRange.Rows.Count + lRowStart - 1
On Error Resume Next ' catch errors
For Each rCell In Range(Cells(lRowStart, iColumn), Cells(lRowEnd,
iColumn))
If rCell < "" Then
' on error uses last value ok may want better error checking
lCur = Right(rCell.Value, Len(rCell.Value) - iCharsToIgnore)
If lCur lMax Then lMax = lCur
End If
Next rCell
findmax = lMax
End Function

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Corey" wrote:

I have a list of values in :
Sheet8.Range("E:E")
that are formatted as:
ST 1234 ' IE "ST_1234" where "_" = Space

I want to place the Maximum Numerical Value +1 into a Textbox on a
Userform,
But to date i have yet worked out how to manage it?

Any One assist me ?
Corey....