match a string to a col & increment by 1

Microsoft Microsoft office excel 2007 -...
March 30, 2010 at 07:31:25
Specs: Windows XP
I have two sheets. I want to match the string in cell a2 sheet 1 to the values in column A of sheet 2. here is the format of string in Sheet 1 column A: Axxxx (A - alphabet a to z, x number 0-9)
Column A sheet 2 - Axxxxxxx(A - alphabet a to z, x number 0-9)
So the purpose is if my string is : A0101 from sheet 1 and I find the following three entries from sheet 2: A0101001, A0101002, A0101003, the result in cell B2 of sheet 1 is A010104. My data is sequential.

See More: match a string to a col & increment by 1

April 4, 2010 at 10:00:31

I think that what you are trying to do is to get the next 'number' in the sequence that you specify.

I have written a user defined function to do this as I couldn't come up with a formula. If someone else comes up with a formula you should use it.

Anyway this user defined function is used as follows:
In Cell A2 is your stem, e.g., A0101
In cell B2 is the user defined function NxtSeq, as follows:


NxtSeq takes two arguments. The first is a range of cells which contain existing alphanumeric values, and the second is the cell that contains the stem - cell A2 in this case.

The range for the existing alphanumeric values should be larger than the used range, so that when new values are added to the list, this formula will automatically update to show the next alphanumeric.

To create the user defined function do this:
From your worksheet, click Alt+f11 (the Alt key and function key #11 together).
This opens the Visual Basic window.
The Project explorer pane should be visible on the left - if not, from the VB menu select View - Project Explorer.
Find your workbook in the Project explorer - like this: 'VBA Project(MyWorkbook.xls)'.
Right-click it and select Insert and Module (not Class module)
Double click the new module name (Module1).

In the VB window on the right enter this:

Option Explicit

Public Function NxtSeq(Source As Range, Base As String) As Variant
Dim dblNum As Double
Dim rngCell As Range
Dim blnFirstMatch As Boolean
Dim rngLrgst As Range
Dim n As Integer

On Error GoTo ErrHnd

'set flag to base not matched
blnFirstMatch = False

'loop through all cells in the range
For Each rngCell In Source.Cells()
    'test if value has the required stem
    If Left(rngCell.Text, Len(Base)) = Base Then
        'if this is the first match for this base, set the Largest Range
        If blnFirstMatch = False Then
            Set rngLrgst = rngCell
            'set flag to show we have an initial range
            blnFirstMatch = True
        End If
        'get the number part of the text, after the base
        dblNum = CDbl(Right(rngCell.Text, Len(rngCell.Text) - Len(Base)))
        'test if it's bigger than the first one we have
        If dblNum > CDbl(Right(rngLrgst.Text, _
                    Len(rngLrgst.Text) - Len(Base))) Then
            'reset the largest range to this cell
            Set rngLrgst = rngCell
        End If
    End If
Next rngCell

'make sure we found a match for the base - if not exit
If blnFirstMatch = False Then NxtSeq = CVErr(xlErrName): Exit Function

'match found so get the largest number found
dblNum = CDbl(Right(rngLrgst.Text, Len(rngLrgst.Text) - Len(Base)))
'increment by 1
dblNum = dblNum + 1
'convert back to text and add it to the base
NxtSeq = Base & Format(dblNum, Application.WorksheetFunction. _
                    Rept("0", Len(rngLrgst.Text) - Len(Base)))
Exit Function

'error handler
'return the NA error
NxtSeq.Value = CVErr(xlErrNA)
End Function

Click Save from the VB menu.
Use Alt+f11 to return to the main Excel window.

Enter the user-defined function with its two parameters.

If the base is not found the function returns the #NAME error.

Hope this works.


Report •
Related Solutions

Ask Question