How to rectify my script to place parsed values in it's right position?

[shahin]

I've written a script in vba to get the last portion of a big string (separated by space). When I execute the script, it does it's job flawlessly by placing those scraped values in it's next column. However, when any blank cells come along it place those values in wrong position. How can I modify my script in such a way so that It will check first whether any cell is blank. If it finds any blank cell then it will skip that and look for the next cell with values and do the operation until the last row?

Script I'm trying with:

Sub splitting_Stringss()
    Dim lrow As Long, cel As Range
    Dim i As Long, item_list As Variant

    lrow = Range("A" & Rows.Count).End(xlUp).Row
    For Each cel In Range("A1:A" & lrow)
        If InStr(1, cel.Value, " ") <> 0 Then
            item_list = Split(cel, " ")
            i = UBound(item_list)
            r = r + 1: Cells(r, 2) = item_list(i)
        End If
    Next cel
End Sub

I tried with the below strings:

   ColumnA

1 NACLE CHURCH RLT
2 HOA RIANO RT OC VBR3
4
5 MID  PIPE CORP CON COY
6 STE DAAS ST STE LIB TX ML

With my macro the result I'm having:

   ColumnA               ColumnB

1 NACLE CHURCH RLT           RLT
2 HOA RIANO RT OC VBR        VBR
3                            COY  'placed in the wrong position
4                            ML    'placed in the wrong position
5 MID  PIPE CORP CON COY
6 STE DAAS ST STE LIB TX ML

My expected output:

   ColumnA                   ColumnB

1 NACLE CHURCH RLT            RLT
2 HOA RIANO RT OC VBR         VBR
3                                 
4                                 
5 MID  PIPE CORP CON COY      COY
6 STE DAAS ST STE LIB TX ML   ML

The leading numbers are the row number.

 

[Marc L]

Bad logic place for r = r +1 !
And useless just respecting TBTO rule : use an Offset from cel …
Paste this code in worksheet module :​

Sub Demo4Noob1()
         Dim Rg As Range, SP$()
    For Each Rg In Me.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants)
                         SP = Split(Rg.Value)
             Rg(1, 2).Value = SP(UBound(SP))
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Sub Demo4Noob2()
         Dim Rg As Range
    For Each Rg In Me.UsedRange.Columns(1).Cells
             Rg(1, 2).Value = Mid$(Rg.Value, InStrRev(Rg.Value, " ") + 1)
    Next
End Sub

You may Like it !
​

 

[shahin]

Hi Marc L!! Thanks for your response and solution. Your solution is just perfect. It does the way I expected. What If I wish to parse the second last portion? I tried with your script but couldn't make any idea to go with.

 

[YasserKhalil]

Based on post #2 see that line

Rg(1, 2).Value = SP(UBound(SP) - 1)

 

[shahin]

Right YasserKhalil. Good catch!! Thanks for the input. Btw, I'm great fan of Marc L's coding style.

 

[shahin]

Hi Yasser, could you do me a favor? Marc L and Narayan both gave me some demo using "Me." this keyword and suggested me to paste it in "worksheet module" before execution. The problem is I'm unable to do so because I don't know where the heck the "worksheet module" located and how to make use of it. So I always use "activesheet." replacing "Me.". However, I'm willing to learn the usage of "worksheet module". My request to you is that if you upload a demo workbook with the macro pasted in, I would be able to learn how to locate that "worksheet module" and how to use it?

 

[Marc L]

In the VBAProject window, ThisWorkbook is the workbook module,
see above for worksheets modules …​

 

[YasserKhalil]

Here's a snapshot that may help you

 

[shahin]

Thanks Yassser, I could do it eventually.

 

[shrivallabha]

FWIW, you can use many styles in Excel to get the same result. Following will achieve the same result without looping through cells.

Public Sub GetRightPart()
With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(0, -1).Value
    .Replace "* ", ""
End With
End Sub

 

[shahin]

Yes, it is working as you said and it ".Replace " *", """ also gives the first portion. Could you elaborate In few words how "Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)" this portion is created. Sorry for my ignorance. Thanks Shrivallabha, for such a nice input.

 

[Marc L]

same result without looping through cells.

Yes, avoiding loop is often the better ...
​

 

[shrivallabha]

Yes, it is working as you said and it ".Replace " *", """ also gives the first portion. Could you elaborate In few words how "Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)" this portion is created. Sorry for my ignorance. Thanks Shrivallabha, for such a nice input.

There are many ways to define Ranges in Excel VBA.

We need to have column B of the same size as A. Column A data begins at row 1 so we use:
Range("B1:B"

Now we need to find out the last row in Column A. This we find out by going down to the last row in the sheet and hitting CTRL+UP from there programmatically by:
Range("A" & Rows.Count).End(xlUp)

Since the object returned by above expression will be a Range we want to just access its Row property so that gives us:
Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)

So at runtime if there are say 100 rows of data in Column A this reference will become
Range("B1:B" & 100)
Range("B1:B100")

Does that help?

 

[shahin]

You made my day, Shrivallabha. It's like a tutorial . Thanks a lot.

 

[Marc L]

Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)

Often no matter to know the last row number as
Range("B1", Cells(Rows.count, 1).End(xlUp)(1, 2)) …
or
Cells(1).CurrentRegion.Columns(2) …

And Cells is a Range too.

Think, But Think Object !
​

 

[shahin]

The way I started could have been accomplished like the following as well:

Sub SplitStrings()
    Dim cel As Range, listitem As Variant

    For Each cel In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If cel.Value <> "" Then
            listitem = Split(cel.Value, " ")
            cel(1, 2) = listitem(UBound(listitem))
        End If
    Next cel
End Sub