1

I am able to extract the first word from a string, using VBA, by searching for the space between two words. The code is like this:

Sub ConnectionTable()

Dim j As Integer
Dim i As Integer


For j = 6 To 8

    For i = 2 To 8
        If (InStr(1, ThisWorkbook.Worksheets("RcmCauses").Cells(j, 5).Value, Left(ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5).Value, InStr(ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5), " ")))) And (ThisWorkbook.Worksheets("RcmCauses").Cells(j, 2).Value Like "* hoofdmotor *") And (ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 2).Value Like "* hoofdmotor *") Then
            ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value = 1 - 1 + j
            ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 6).Value = ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value
            j = j + 1
            i = 2
            
        ElseIf (InStr(1, ThisWorkbook.Worksheets("RcmCauses").Cells(j, 5).Value, Left(ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5).Value, InStr(ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5), " ")))) And (ThisWorkbook.Worksheets("RcmCauses").Cells(j, 2).Value Like "* noodmotor *") And (ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 2).Value Like "* noodmotor *") Then
            ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value = 1 - 1 + j
            ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 6).Value = ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value
            j = j + 1
            i = 2
         
        ElseIf (InStr(1, ThisWorkbook.Worksheets("RcmCauses").Cells(j, 5).Value, Left(ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5).Value, InStr(1, ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5), " ")))) Then
            ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value = 1 - 1 + j
            ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 6).Value = ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value
            j = j + 1
            i = 1
        Else
            ThisWorkbook.Worksheets("RcmCauses").Cells(j, 6).Value = 1 - 1 + j

        End If
        
    Next i
   
Next j
End Sub

Everthing goes well until I come accross a string that only consist of one word, so no space can be found (see picture below). What happens is that the second elseif-statement suddenly becomes true, while it supposed to be false. How can I still extract one word from a string that only contains one word while other strings contain multiple words?

Screenshot of strings

Dominique
  • 16,450
  • 15
  • 56
  • 112
Tessa
  • 53
  • 6
  • 2
    Can you elaborate which string you want from what under which condition? Also: Are you aware about the VBA `Split`-function? – FunThomas May 02 '22 at 16:47
  • Consider using worksheet variables to make your code more readable. It's generally considered bad practice to alter the loop counter (i, j) inside of a For/Next loop: if you find you need to do that then you probably need a different type of loop. – Tim Williams May 02 '22 at 16:48
  • Each row of column E contains a string. With InStr(1, ThisWorkbook.Worksheets("RcmCausesWantij").Cells(i, 5), " ") I extract from E2 "Elektromotor:", from E3 "Elektromotor:", from E4 "Koppeling:", etc. It goes well until I reach E6 where no space can be found in the string. So the second elseif-statement is not working properly. – Tessa May 02 '22 at 16:53
  • @Tessa could you rearrange your code to make it readable? – Vitalizzare May 02 '22 at 17:07
  • 3
    Consider using the snippet `Split(Expression:=StrItem,Delimiter:=" ")(0)` to find the first word of a string, regardless of if it has a space in it; You can test the functionality with the following immediate window snippet `For each strItem in Array("Panamaweil: 1 Oost (n=2)", "Reductiekast"): ?Split(Expression:=StrItem,Delimiter:=" ")(0):Next` – Taylor Alex Raine May 02 '22 at 17:09
  • 1
    The split-function did the trick. Thank you @FunThomas. – Tessa May 02 '22 at 17:11
  • @Tessa now that you are using Split, check on the use of UBound, it may end up beneficial in this or another similar solution. – Ricardo A May 02 '22 at 17:18
  • Other than `Split`, you could also use the `RegExp` Object for this and similar string parsing projects. Using Regular Expressions, you can create a matching pattern to search a string for matches. In this case, your pattern would be like `[^\s]+` which translates to "one word of any length (non-alphabet characters included)". Or `\w+` which would be "one word of any length (alphabet characters only). – Toddleson May 02 '22 at 17:30
  • @Toddleson also a nice suggestion, thank you very much. – Tessa May 02 '22 at 18:10

0 Answers0