Finding index of string in string array

Question

This program removes a string from an array to a new sheet. I find the string 'hello' or 'bye' but I also want the string in the index before each of those strings. The string before 'hello' or 'bye' will not always be the same so how can the I use the Index() function?

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

I'm not sure I understand. Can you include both the current and expected results? — David Rushton
– David Rushton, Commented Jul 1, 2016 at 16:18
dim s as string: s = left(1, range("d1").value, ", hello", vbtextcompare)) should do it. See InStr function to return the position of a search term within a longer string. — user4039065
– user4039065, Commented Jul 1, 2016 at 16:20
I updated the question @destination-data. The expected result would be the name coming before the string on the new sheet. — johndoe253
– johndoe253, Commented Jul 1, 2016 at 16:27
@Jeeped I tried that but it says "wrong number of arguments or invalid property assignment" — johndoe253
– johndoe253, Commented Jul 1, 2016 at 16:27

score 2 · Accepted Answer · 2016-07-01 17:09:05Z

2

While the InStr function is typically used to locate a substring within a string, your multiple search terms may be better handled with the 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

Note that the search terms use on the split are case-sensitive.

Addendum for revised question:

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

edited Jul 1, 2016 at 17:09

answered Jul 1, 2016 at 16:40

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

johndoe253 Over a year ago

I appreciate the help. This is sample data but if I were to have more strings in that cell how would I go about removing the 'hello' or 'bye' along with the word before it if it happened to be in the middle of a cell full of strings?

user4039065 Over a year ago

See my addendum above. Please make revisions to your original question or post a new question. Avoid making this a Russian Doll Question.

johndoe253 Over a year ago

I appreciate the help. I'll post a new question later and try your method.

Collectives™ on Stack Overflow

Finding index of string in string array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related