Excel Vba -字符串中的组号序列

Question

我有一个代码，它从定义的单元格返回所有匹配值的数字序列字符串。图示如下：

COL A    COL B  COL C
Item No  Data   Unique Data
1        A      A
2        A      B
3        A
4        B
5        B
6        A
7        A
8        B
9        B
10       B

单元格D2 =查找序列(C2，B2:B11，A2:A11)将返回1, 2, 3, 6, 7

单元格D3 =查找序列(C3，B2:B11，A2:A11)将返回4, 5, 8, 9, 10

然而，我想要的结果是；

细胞生长因子( D2 -> 1-3, 6-7 )

细胞生长因子( D3 -> 4-5, 8-10 )

下面是我使用的函数的代码：

Function Lookupsequence(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
result = result & ", " & Return_val_col.Cells(i, 1).Value

End If
Next
Lookupsequence = Trim(result)
End Function

Answer 1

试试这个：

Function Lookupsequence(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)
Dim i As Long
Dim result As String
Dim initial As String
Dim separator As String
Dim preValue As Integer
Dim value As Integer

preValue = -1
separator = ""
For i = 1 To Search_in_col.Count
    value = CInt(Return_val_col.Cells(i, 1).value)
    If Search_in_col.Cells(i, 1) = Search_string Then
        If value - 1 = preValue Then
            result = initial & "-" & value
        Else
            result = result & separator & value
            initial = result
            separator = ","
        End If
        preValue = value
    End If
Next
Lookupsequence = Trim(result)
End Function