vba - 如何仅提取VBA中的特定字符串

Question

我已经在 Extract strings from one text cell in VBA 上问过一个问题.首先，对于提出一个可能会占用您更多时间的具有挑战性的问题，我深表歉意。

基本上它是一个从一个单元格中提取主机名和 IP 地址的宏，我必须从一列中的许多这样的单元格中提取。 @Nicolas 已经帮助我编写了一个非常好的脚本。但是，由于我的输入数据的变化，IP 地址和主机名的格式是非常不可预测的。作为旁注，可以在@Nicolas 的答案下找到该代码。

现在我收到以下错误消息:

Subscript out of range

在线 tempIps = Trim(Split(lineList(line), ":")(1))因为有时输入数据如下所示，其中包含关键字但没有我需要的真实主机名和 IP 地址。

Hostname Rack Key IP Address Application Name

有时在 For Index = 1 To UBound(hostList)因为没有找到作为关键字的主机名或 IP 地址。

以下是一些示例供您引用，如果需要，我可以提供更多:

示例 1:

Please refer long description 22:00 to 01:00 AM

MW SA to draw password for the following servers.

Hostname : a01gibapp1a IP Address : 10.89.96.21 Privileged ID : root  

Hostname : a01gibweb1a IP Address : 10.89.75.23 Privileged ID : root  

01:00 to 03:00 AM

MW SA to run the script implementation.sh which is under /staginapp/jul2015/20jul15/ in all the IB app Servers.
MW SA to run the script implementation.sh which is under /tmp/CH64698/ in all the web servers.
MW SA to restart the WebSphere instances one by one in all the IB App servers given above.
MW SA to restart the IB IHS instances if required.

03:00 to 04:00
AMS technical verification
BU Live Verification.

示例 2:

Please refer to long description & attachment Implementation steps:
=====================
Schedule time: 20th July 10PM to 21st July 3AM

Allow MW SA to draw the root password for below servers.
a01gibpns1a 10.89.71.26
a01gibpns2a 10.89.71.27
a01gibpns3a 10.89.71.34
a01gibpns4a 10.89.71.33

a01ribpns1a 10.89.35.83

这是我的代码的全部部分:

Public Sub splitHostnameAndIPAddress()

Dim addressStream As String

Dim lineList() As String
Dim line As Integer
Dim tempHosts, tempIps As String
Dim hostList(), ipList() As String
Dim hostIndex, ipIndex, tempIndex As Integer
Dim result As String
Dim ipFlag As Boolean
Dim X As Integer, wslasteow As Integer

With Sheets(1)

wSlastRow = .Rows(.Range("W:W").Rows.Count).End(xlUp).Row

For X = 4 To wSlastRow

hostIndex = 1
ipIndex = 1

'Get address string from cell
addressStream = .Range("W" & X)

'Split by vbLf(line by line)
lineList = Split(addressStream, vbLf)

'Loop all line
For line = 0 To UBound(lineList)

    'If "IP Address" string include in line, store ip address
    'If InStr(lineList(line), "IP Address") Or InStr(lineList(line), "IP ADDRESS") Or InStr(lineList(line), "IP") Then
     If InStr(lineList(line), "IP Address") Or InStr(lineList(line), "IP ADDRESS") Or InStr(lineList(line), "IP Address ") Then

        'Check for getting right pair.
        If ipFlag Then
            hostIndex = hostIndex + 1
        Else
            ipFlag = True
        End If

        'Getting Ip(s)
        tempIps = Trim(Split(lineList(line), ":")(1))

        'If there is several ip in string which are separated by ","
        If InStr(tempIps, ",") Then

            'Loop ip list which is separated by "," and store
            For tempIndex = 0 To UBound(Split(tempIps, ","))

                ReDim Preserve ipList(ipIndex)

                ipList(ipIndex) = Trim(Split(tempIps, ",")(tempIndex))

                ipIndex = ipIndex + 1

            Next tempIndex

        'Else single ip is store
        Else

            ReDim Preserve ipList(ipIndex)

            ipList(ipIndex) = tempIps

            ipIndex = ipIndex + 1

        End If


    'If "Hostnames" string include in line, store host name
    ElseIf InStr(lineList(line), "Hostname:") Or InStr(lineList(line), "HostName:") Or InStr(lineList(line), "HostName :") Then

        'Check for getting right pair.
        If ipFlag Then
            ipFlag = False
        Else
            ipIndex = ipIndex + 1
        End If

        'Getting host(s)
        tempHosts = Trim(Split(lineList(line), ":")(1))

        'If there is several host in string which are separated by ","
        If InStr(tempHosts, ",") Then

            'Loop host list which is separated by "," and store
            For tempIndex = 0 To UBound(Split(tempHosts, ","))

                ReDim Preserve hostList(hostIndex)

                hostList(hostIndex) = Trim(Split(tempHosts, ",")(tempIndex))

                hostIndex = hostIndex + 1

            Next tempIndex

        'Else single host is store
        Else

            ReDim Preserve hostList(hostIndex)

            hostList(hostIndex) = tempHosts

            hostIndex = hostIndex + 1

        End If

    End If

Next line

'Adjust two list size
If hostIndex > ipIndex Then
    ReDim Preserve ipList(hostIndex - 1)
ElseIf ipIndex > hostIndex Then
    ReDim Preserve hostList(ipIndex - 1)
End If


'Loop host list
For Index = 1 To UBound(hostList)

    'Add host & ip pair
    result = result & ipList(Index) & vbTab & hostList(Index) & vbNewLine

Next Index


'Show result
Sheets(2).Range("A" & (X)).Value = result

Next X
End With

End Sub

Answer 1

您是否可以不只检查字符串，以查看您尝试分割字符串的字符在分割之前是否实际上在字符串中？

If InStr(lineList(Line), ":") Then
    tempIps = Trim(Split(lineList(Line), ":")(1))
End If

如果没有要拆分的冒号，这将阻止代码尝试拆分字符串。

让我知道我是否错误地解释了您所追求的内容。