arrays - 查找字符串数组中字符串的索引

Question

该程序将字符串从数组中删除到新工作表中。我找到字符串“hello”或“bye”，但我还希望索引中每个字符串之前的字符串。 “hello”或“bye”之前的字符串并不总是相同，那么我如何使用 Index() 函数？

Sub SplitWithFormat()
    Dim R As Range, C As Range
    Dim i As Long, V As Variant
    Dim varHorizArray As Variant
    Dim rge As Range
    Dim intCol As Integer
   
Set R = Range("d1", Cells(Rows.Count, "d").End(xlUp))
For Each C In R
    With C
        .TextToColumns Destination:=.Range("AD1"), DataType:=xlDelimited, _
        consecutivedelimiter:=True, Tab:=False, semicolon:=True, comma:=False, _
        Space:=True, other:=True, Otherchar:=vbLf

        Set rge = Selection
        varHorizArray = rge
        .Copy
        Range(.Range("AD1"), Cells(.Row, Columns.Count).End(xlToLeft)).PasteSpecial xlPasteFormats
    End With
Next C

Application.CutCopyMode = False

    For intCol = LBound(varHorizArray, 2) To UBound(varHorizArray, 2)
       Debug.Print varHorizArray(1, intCol)
    Next intCol
    
       
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    varHorizArray = Array("bye","hello")
    Set NewSh = Worksheets.Add

    With Sheets("Sheet2").Range("AD1:AZ100")

        Rcount = 0

        For i = LBound(varHorizArray) To UBound(varHorizArray)

            
            Set Rng = .find(What:=varHorizArray(i), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rcount = Rcount + 1

                    Rng.Copy NewSh.Range("A" & Rcount)

                    
                    NewSh.Range("A" & Rcount).Value = Rng.Value

                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next i
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Answer 1

而InStr function通常用于定位字符串中的子字符串，使用 Split function 可以更好地处理多个搜索词。 .

Option Explicit

Sub stripName()
    Dim rw As Long

    With ActiveSheet
        For rw = 1 To .Cells(Rows.Count, "D").End(xlUp).Row
            .Cells(rw, "A") = Split(Split(.Cells(rw, "D").Value2, ", hello")(0), ", bye")(0)
        Next rw
    End With

End Sub

请注意，拆分中使用的搜索词区分大小写。

修订问题的附录:

Option Explicit

Sub stripName()
    Dim rw As Long, s As String

    With ActiveSheet
        For rw = 1 To .Cells(Rows.Count, "D").End(xlUp).Row
            s = Split(.Cells(rw, "D").Value2, ", bye")(0)
            s = Split(s, ", hello")(0)
            s = Split(Chr(32) & s, Chr(32))(UBound(Split(Chr(32) & s, Chr(32))))
            .Cells(rw, "A") = s
        Next rw
    End With

End Sub