EXCEL 电子位号 补全 如（R83-85）

Sub 按钮1_Click()
    Dim strD As String
    Dim globalIndex As Integer
    globalIndex = 2
    Set mysheet1 = ThisWorkbook.Worksheets("Sheet3")
    While mysheet1.Cells(globalIndex, 4) <> ""
         strD = mysheet1.Cells(globalIndex, 4)
      mysheet1.Cells(globalIndex, 4) = FormatNUM(strD)
        globalIndex = globalIndex + 1
    Wend

End Sub


Public Function FormatNUM(key As String) As String
    FormatNUM = ""
    Dim nKey As String
    Dim sStart As String
    Dim sEnd As String
    Dim indexInt As Integer
    
    aArray = Split(key, " ")
    For i = 0 To UBound(aArray)
        Item = aArray(i)
        bArray = Split(Item, "-")
       If UBound(bArray) > 0 Then '有-的处理没有就不处理
            sStart = bArray(0)
            sEnd = bArray(1)
            indexInt = index(sStart)
            Prefix = Mid(sStart, 1, indexInt - 1) '前缀
            StartInt = CInt(Mid(sStart, indexInt)) '开始位号
            EndInt = CInt(sEnd) '结束位号
            'MsgBox (StartInt)
            'MsgBox (EndInt)
            'MsgBox (Prefix)
            Dim nextPlace As Integer
            FormatNUM = FormatNUM & sStart & ","
            For j = StartInt + 1 To EndInt
                 If j = EndInt Then
                    FormatNUM = FormatNUM & Prefix & CStr(j)
                 Else
                    FormatNUM = FormatNUM & Prefix & CStr(j) & ","
                 End If
                 
            Next j
            'MsgBox (bArray)
       Else
            If i = UBound(aArray) Then
                FormatNUM = FormatNUM & Item
            Else
                FormatNUM = FormatNUM & Item & ","
            End If
             
       End If
    Next i
    
End Function


Public Function index(firstStr As String) As Integer
    Dim lastBeginIndex As Boolean '是否是前缀的数字
    lastBeginIndex = True
    Dim strNum As String
    For i = 1 To Len(firstStr)
        strNum = Mid(firstStr, i, 1)
      
        
            If IsNumeric(strNum) Then '这个方法找是否为数字字符找到数字的位置
                If lastBeginIndex Then
                index = i
                lastBeginIndex = False
                End If
            Else
                lastBeginIndex = True
            End If
        
    Next i
End Function