How do I modifiy lines of data in a loop?

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
In an Excel file I've got, the lines of data read as in this example:

R13,15,18-22,35

I need to have the data appear as follows:

R13,R15,R18,R19,R20,R21,R22,R35

I think I need some kind of loop or even a loop within a loop in order to preface each comma-delimited data item with a letter, and also to further break out the data item that reads 18-22, but I honestly have no idea of how to set that up in code, while also plowing through all lines of data.

Almost forgot.... there are also records that have no data at all.

If any one is willing to help a loop-challenged person, I would be very appreciative! Thanks in advance to those who reply....looking forward to it!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Where is this data located?

Is it all in one cell?
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Hi, Norie,

WOW!! What a fast response! The data is located in one cell. Sorry I didn't specify that in my initial post. :oops:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
This appears to work for your small example.
Code:
Sub ExpandData()
Dim rng As Range
Dim arrItems
Dim arrVals
Dim I As Long
Dim J As Long
Dim X As Long
   
    Set rng = Range("A1")
    While rng.Value <> ""
        arrItems = Split(rng.Value, ",")
        Range("B" & rng.Row) = arrItems(LBound(arrItems))
       
        For I = LBound(arrItems) + 1 To UBound(arrItems)
           
            arrVals = Split(arrItems(I), "-")
           
            For J = arrVals(LBound(arrVals)) To arrVals(UBound(arrVals))
                X = X + 1
                Range("B" & rng.Row).Offset(, X) = Left(arrItems(LBound(arrItems)), 1) & J
            Next J
               
        Next I
        Set rng = rng.Offset(1)
    Wend
   
End Sub
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38

ADVERTISEMENT

Norie,

What took you so long to reply?? :wink:

Thanks so much for the code snippet.... I'll work with it against my data and see how it comes out, and then let you know.

But once again, thanks for your fast reply and willingness to help. It's greatly appreciated and helps the learning curve. (y)
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Hi, Norie,

Not sure if I mentioned in my initial post, but some of the cells in question in my file can sometimes contain NO data. The column I am working with is column "C". So..... at the risk of asking for way too much, how would I modify your snippet to account for sometimes having no value in the cell, but other times, having a value in the cell?

Also, would I be correct in saying I should also check another cell for EOF? A cell that always has data in it?

If I am asking for too much, please let me know, because I don't expect you or anyone to do my work for me, but I'm dying to learn as fast as I can.

Thanks again!!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you mean that there might be gaps in the data?

If so what you want to do is loop in a different way.

First you would want to find the last row with data.

There are various ways to do that, here's one.
Code:
LastRow = Range("C" & Rows.Count).End(xlUp).Row
This could then be used in a For Next loop.
Code:
For I = 1 To LastRow ' change 1 if there's a header row
     Set rng = Range("C" & I)
     If rng.Value<>"" Then ' is there data 
           ' rest of code
     End If
Next I
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Hi, again, Norie....

It looks like it's beginning to do what I have in mind, except the output is taking each comma-separated value and placing that into separate cells.... what I actually am looking for is to have the output data all in one cell, since the input data all came from one cell.

And really, thanks for your help to date - that's a lifesaver when the headaches pile up!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
That shouldn't be too difficult.:)

Could you post some more sample data?

I'm just off but I'll check back later.
 

Forum statistics

Threads
1,141,625
Messages
5,707,470
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top