 0
Thanks

A few words of thanks would be greatly appreciated.

# Excel - Match a string to a column & increment by 1 ## Issue

I have two sheets with data as follows:

Sheet :

String, result
A0101

Format of string in Sheet 1 column A: Axxxx (A - alphabet a to z, x number 0-9). the Alphabet is the series starter.

Sheet 2:

```A0101001
B0102001
Q9911001
S7834001
A0101002
A0101003 ```

Column A sheet 2 - Axxxxxxx(A - alphabet a to z, x number 0-9)
the last three numbers are in sequence for each of the first five charcters.

as in the data above:
```A0101001
A0101002
A0101003```

I want to match the string in cell A2 sheet 1 to the values in column A of sheet 2.
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 A0101004. My data is sequential.

## Solution

I have added one more data in A3 in sheet 1
B0102
In sheet 2 row is column headings and data is below
string:

```A0101001
B0102001
Q9911001
S7834001
A0101002
A0101003
```

Then try this macro"test". If you want to recheck first run mcaro "undo" and then "test"

```Sub test()
Dim r As Range, c As Range, x As String
Dim j As Integer, cfind As Range
Dim y As String, add As String
j = 0
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
x = c.Value
With Worksheets("sheet2")
Set cfind = .Cells.Find(what:=x, lookat:=xlPart)
If cfind Is Nothing Then GoTo nnext

j = j + 1
'msgbox  j
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then GoTo nnext
j = j + 1
'msgbox  j
Loop
line1:
y = Mid(cfind, 6, 2)
End With 'sheet2
c.Offset(0, 1) = x & y & j + 1

nnext:
j = 0
Next c

End With

End Sub

Sub undo()
With Worksheets("sheet1")
Range(.Range("B2"), .Range("B2").End(xlDown)).Clear
End With
End Sub```

## Note

Thanks to venkat1926 for this tip.
0
Thanks

A few words of thanks would be greatly appreciated.