Can't fix errors caused by scooping value from space-separated strings

[shahin]

I've written a macro to scoop certain portion from strings separated by space. It was doing good until there is no such match found in certain strings. If the macro doesn't find any string meeting the aforesaid criteria within the defined Range then it breaks. How can I fix this error from happening. I tried to take the value separated by second last space.

If we consider the following example as a test then my macro will break when it hits the cell containing "Keh Pan" because I intended to pick the value separated by second last space and place those to its adjacent cell.

This is where I'm applying my macro:

Margaret C Hernon Tr
Wdg Cambridge St Rt
Keh Pan
Li Tan Pham

This is how I wanted the output to be (see the adjacent column):

Margaret C Hernon Tr    C
Wdg Cambridge St Rt     Cambridge
Keh Pan              'code breaks here
Li Tan Pham

The macro I'm trying with:

Sub separate_certain_portions()
    Dim r As Range, pstr As Variant
  
    For Each r In Range("A1:A4")
        pstr = Split(r)
'        If pstr(UBound(pstr) - 2) <> "" Then
            r(1, 2) = pstr(UBound(pstr) - 2)
'        End If
    Next r
End Sub

 

[YasserKhalil]

May be

Sub Separate_Certain_Portions()
    Dim r  As Range
    Dim x  As Long

    For Each r In Range("A1:A4")
        x = InStr(r, " ") + 1
        r(1, 2) = Split(Mid(r, x))(0)
    Next r
End Sub

 

[shahin]

It worked against above strings. However, it will give wrong values when the string is:

"Margaret C Hernon Tr Lt Hwr"

If I run your suggested macro, I will get "C" whereas I'm expecting "Tr".

 

[YasserKhalil]

I don't get your logic here .. I thought you need the second part only
Can you explain the logic .. why "Tr" is expected here?

 

[shahin]

I am trying to scoop the portion separated by second last space. From this string:

"Margaret C Hernon Tr Lt Hwr":

Last space separated value = "Lt"
Second last space separated value = "Tr"
Third last space separated value = "Hernon"

 

[YasserKhalil]

What about this case "Keh Pan" what is the result do you expect?

 

[shahin]

Nothing from the above string as it doesn't meet the criteria. Btw, the macro I've pasted above can deal with that If I use "On error resume next". However, I hate to use that line.

 

[YasserKhalil]

Try this code

Sub Separate_Certain_Portions()
    Dim y  As Variant
    Dim r  As Range
    Dim x  As Long

    For Each r In Range("A1:A5")
        x = InStrRev(r, " ") + 1
        y = UBound(Split(Trim(Mid(r, 1, x - 1)))) - 1

        If UBound(Split(r, " "), 1) = 1 Then
            r(1, 2) = ""
        Else
            r(1, 2) = Split(Trim(Mid(r, 1, x - 1)))(y)
        End If
    Next r
End Sub

 

[shahin]

Yes, it is working. Thanks. However, can you tell me where I was going wrong with the first macro I've pasted above?

 

[YasserKhalil]

It is related to the split function as you subtracted 2 so you got an error if ubound value was 2 or 1 ..

 

[shahin]

Yes, that is. I used a conditional statement to avoid any such string not fulfilling the criteria, but it didn't work.

 

[YasserKhalil]

If you would like to put conditional statements you have to calculate all the possibilities .. and manipulate the string according

 

[shahin]

Here comes the real vba talk.

 

[YasserKhalil]

These conditional statements is so simple not difficult at all ..Try using F8 while running the code to run the code line by line .. I have learned a lot through this approach

 

[Debaser]

You only needed to test the UBound of pstr, remembering that it is indexed from 0:

        pstr = Split(r)
        If UBound(pstr) > 1 Then
           r(1, 2) = pstr(UBound(pstr) - 2)
        End If

 

[shahin]

Wow!! This is great. What an incredible fix!! Thanks Debaser.