Previous Topic Next Topic

Match a string to a col & increment by 1

Solved/Closed
seema_gu14 - Mar 30, 2010 at 09:39 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 30, 2010 at 09:48 PM
I have two sheets with data as follows:

Sheet 1

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
The number will never jump to 005 for the string A0101

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.
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 30, 2010 at 09:48 PM
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))
'msgbox  r.Address
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
add = cfind.Address
'msgbox  j
'msgbox  add
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then GoTo nnext
If cfind.Address = add Then GoTo line1
'msgbox  cfind.Address
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
0