Need to Loop Through the values in Cells and Extract Parts

[rdavis7408]

I have a file with a column with a cells in it that each have multiple
values in each cell. So there may be 400 rows in the column and within
each cell there may be 40 unique values in it. For example : cell A1
would have the following:


a:37:{i:0;s:3:""500"";i:1;s:3:""503"";i:2;s:3:""506"";i:3;s:
3:""508"";i:4;s:3:""511"";i:5;s:3:""514"";i:6;s:3:""517"";i:7;s:
3:""519"";i:8;s:3:""522"";i:9;s:3:""525"";i:10;s:3:""528"";i:11;s:
3:""531"";i:12;s:3:""533"";i:13;s:3:""536"";i:14;s:3:""539"";i:15;s:
3:""542"";i:16;s:3:""544"";i:17;s:3:""547"";i:18;s:3:""550"";i:19;s:
3:""553"";i:20;s:3:""556"";i:21;s:3:""558"";i:22;s:3:""561"";i:23;s:
3:""564"";i:24;s:3:""567"";i:25;s:3:""569"";i:26;s:3:""572"";i:27;s:
3:""575"";i:28;s:3:""578"";i:29;s:3:""581"";i:30;s:3:""583"";i:31;s:
3:""586"";i:32;s:3:""589"";i:33;s:3:""592"";i:34;s:3:""594"";i:35;s:
3:""597"";i:36;s:3:""599"";}

I need to create a column in a new spreadsheet that will have a column
in it with all the values that are within the "" "" and then move to
the next cell to repeat.

So I can loop through the column, but do not know how to loop through
the value of a cell and assign it to a value to be pasted in another
worksheet.

Can anyone point me in the right direction? Thank you so much.

Robert

 

[Ron Rosenfeld]

I have a file with a column with a cells in it that each have multiple
values in each cell. So there may be 400 rows in the column and within
each cell there may be 40 unique values in it. For example : cell A1
would have the following:


a:37:{i:0;s:3:""500"";i:1;s:3:""503"";i:2;s:3:""506"";i:3;s:
3:""508"";i:4;s:3:""511"";i:5;s:3:""514"";i:6;s:3:""517"";i:7;s:
3:""519"";i:8;s:3:""522"";i:9;s:3:""525"";i:10;s:3:""528"";i:11;s:
3:""531"";i:12;s:3:""533"";i:13;s:3:""536"";i:14;s:3:""539"";i:15;s:
3:""542"";i:16;s:3:""544"";i:17;s:3:""547"";i:18;s:3:""550"";i:19;s:
3:""553"";i:20;s:3:""556"";i:21;s:3:""558"";i:22;s:3:""561"";i:23;s:
3:""564"";i:24;s:3:""567"";i:25;s:3:""569"";i:26;s:3:""572"";i:27;s:
3:""575"";i:28;s:3:""578"";i:29;s:3:""581"";i:30;s:3:""583"";i:31;s:
3:""586"";i:32;s:3:""589"";i:33;s:3:""592"";i:34;s:3:""594"";i:35;s:
3:""597"";i:36;s:3:""599"";}

I need to create a column in a new spreadsheet that will have a column
in it with all the values that are within the "" "" and then move to
the next cell to repeat.

So I can loop through the column, but do not know how to loop through
the value of a cell and assign it to a value to be pasted in another
worksheet.

Can anyone point me in the right direction? Thank you so much.

Robert

Do you want to have all the results in the same cell? Or a different unique
value in different cells in the same row?

If in the same cell, what do you want to use for a separator.

You can use regular expressions to extract each match.

Here's one approach using Regular Expressions to return each of the values.
This one does it into separate cells in the same row; if you wanted to have
them all in the same cell, you would only have to modify the section that pulls
out the results.

====================================
Option Explicit
Sub ReInDoubleQuotes()
Dim rSrc As Range, c As Range
Dim str As String
Dim re As Object, mc As Object
Dim i As Long

Set rSrc = Selection 'or whatever

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = """""([\s\S]*?)"""""

For Each c In rSrc

'clear out old data in destination area
'could define destination as being on a new sheet
Range(c(1, 2), c(1, 50)).ClearContents

If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
For i = 1 To mc.Count
c(1, i + 1).Value = mc(i - 1).submatches(0)
Next i
End If
Next c
End Sub
==================================
--ron

 

[Ron Rosenfeld]

I have a file with a column with a cells in it that each have multiple
values in each cell. So there may be 400 rows in the column and within
each cell there may be 40 unique values in it. For example : cell A1
would have the following:


a:37:{i:0;s:3:""500"";i:1;s:3:""503"";i:2;s:3:""506"";i:3;s:

I need to create a column in a new spreadsheet that will have a column
in it with all the values that are within the "" "" and then move to
the next cell to repeat.

So I can loop through the column, but do not know how to loop through
the value of a cell and assign it to a value to be pasted in another
worksheet.

Can anyone point me in the right direction? Thank you so much.

Robert

Do you want to have all the results in the same cell? Or a different unique
value in different cells in the same row?

If in the same cell, what do you want to use for a separator.

You can use regular expressions to extract each match.

Here's one approach using Regular Expressions to return each of the values.
This one does it into separate cells in the same row; if you wanted to have
them all in the same cell, you would only have to modify the section that pulls
out the results.

====================================
Option Explicit
Sub ReInDoubleQuotes()
Dim rSrc As Range, c As Range
Dim str As String
Dim re As Object, mc As Object
Dim i As Long

Set rSrc = Selection 'or whatever

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = """""([\s\S]*?)"""""

For Each c In rSrc

'clear out old data in destination area
'could define destination as being on a new sheet
Range(c(1, 2), c(1, 50)).ClearContents

If re.test(c.Value) = True Then
Set mc = re.Execute(c.Value)
For i = 1 To mc.Count
c(1, i + 1).Value = mc(i - 1).submatches(0)
Next i
End If
Next c
End Sub
==================================
--ron

 

[Rick Rothstein]

Here is a non Regular Expression alternative to Ron's subroutine...

Sub DistibuteValues()
Dim X As Long
Dim C As Range, Source As Range, Destination As Range
Dim Data() As String

Set Source = Selection ' Or whatever
Set Destination = Range("D1") ' First Cell to receive a value

For Each C In Source
Data = Split(C.Value, """""")
Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
For X = 1 To UBound(Data) Step 2
C.Offset(, (X + 1) / 2).Value = Data(X)
Next
Set Destination = Destination.Offset(1)
Next
End Sub

 

[rdavis7408]

Here is a non Regular Expression alternative to Ron's subroutine...

Sub DistibuteValues()
  Dim X As Long
  Dim C As Range, Source As Range, Destination As Range
  Dim Data() As String

  Set Source = Selection              ' Or whatever
  Set Destination = Range("D1")       ' First Cell to receive avalue

  For Each C In Source
    Data = Split(C.Value, """""")
    Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
    For X = 1 To UBound(Data) Step 2
      C.Offset(, (X + 1) / 2).Value = Data(X)
    Next
    Set Destination = Destination.Offset(1)
  Next
End Sub

Thank you very much. One last thing can you tell me how to change the
destination variable to another workbook so that the extracted values
go to another worksheet in the workbook named "Sheet1"?

Thanks

 

[Rick Rothstein]

First off, I apparently posted the wrong code snippet originally... the code
was supposed to start placing values at the cell that the Destination range
was set to (D1 in my coded example), except that it didn't do that... it
simply placed values starting offset 1 from the data cell. So, first off,
here is the code I should have posted initially...

Sub DistibuteValues()
Dim X As Long
Dim C As Range, Source As Range, Destination As Range
Dim Data() As String

Set Source = Selection ' Or whatever
Set Destination = Range("c15") ' First Cell to receive a value

For Each C In Source
Data = Split(C.Value, """""")
Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
For X = 1 To UBound(Data) Step 2
Destination.Offset(, (X - 1) / 2).Value = Data(X)
Next
Set Destination = Destination.Offset(1)
Next
End Sub

Now, to answer your current question, change the value that the Destination
value is set to so that it includes the workbook and worksheet names. You
didn't tell us the worksheet name to use in your workbook that is named
"Sheet1" (seems like an odd workbook name by the way), so I will use a
worksheet name of DistributedValues so it will be obviously different from
the workbook name. Give this a try...

Sub DistibuteValues()
Dim X As Long
Dim C As Range, Source As Range, Destination As Range
Dim Data() As String

Set Source = Selection ' Or whatever
Set Destination = Workbooks("Sheet1.xls").Worksheets( _
"DistributedValues").Range("D1")

For Each C In Source
Source.Parent.Activate
Data = Split(C.Value, """""")
Workbooks("Sheet1.xls").Activate
Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
For X = 1 To UBound(Data) Step 2
Destination.Offset(, (X - 1) / 2).Value = Data(X)
Next
Set Destination = Destination.Offset(1)
Next
End Sub

Make sure you change the workbook and worksheet names as appropriate in the
Set statement for the Destination range (as before, the cell address in the
Range statement on that line is the first cell to receive data).

--
Rick (MVP - Excel)

Here is a non Regular Expression alternative to Ron's subroutine...

Sub DistibuteValues()
Dim X As Long
Dim C As Range, Source As Range, Destination As Range
Dim Data() As String

Set Source = Selection ' Or whatever
Set Destination = Range("D1") ' First Cell to receive a value

For Each C In Source
Data = Split(C.Value, """""")
Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
For X = 1 To UBound(Data) Step 2
C.Offset(, (X + 1) / 2).Value = Data(X)
Next
Set Destination = Destination.Offset(1)
Next
End Sub

Thank you very much. One last thing can you tell me how to change the
destination variable to another workbook so that the extracted values
go to another worksheet in the workbook named "Sheet1"?

Thanks

 

[rdavis7408]

First off, I apparently posted the wrong code snippet originally... the code
was supposed to start placing values at the cell that the Destination range
was set to (D1 in my coded example), except that it didn't do that... it
simply placed values starting offset 1 from the data cell. So, first off,
here is the code I should have posted initially...

Sub DistibuteValues()
  Dim X As Long
  Dim C As Range, Source As Range, Destination As Range
  Dim Data() As String

  Set Source = Selection              ' Or whatever
  Set Destination = Range("c15")       ' First Cell to receive a value

  For Each C In Source
    Data = Split(C.Value, """""")
    Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
    For X = 1 To UBound(Data) Step 2
      Destination.Offset(, (X - 1) / 2).Value = Data(X)
    Next
    Set Destination = Destination.Offset(1)
  Next
End Sub

Now, to answer your current question, change the value that the Destination
value is set to so that it includes the workbook and worksheet names. You
didn't tell us the worksheet name to use in your workbook that is named
"Sheet1" (seems like an odd workbook name by the way), so I will use a
worksheet name of DistributedValues so it will be obviously different from
the workbook name. Give this a try...

Sub DistibuteValues()
  Dim X As Long
  Dim C As Range, Source As Range, Destination As Range
  Dim Data() As String

  Set Source = Selection              ' Or whatever
  Set Destination = Workbooks("Sheet1.xls").Worksheets( _
                    "DistributedValues").Range("D1")

  For Each C In Source
    Source.Parent.Activate
    Data = Split(C.Value, """""")
    Workbooks("Sheet1.xls").Activate
    Destination.Resize(, Columns.Count - Destination.Column + 1).Clear
    For X = 1 To UBound(Data) Step 2
      Destination.Offset(, (X - 1) / 2).Value = Data(X)
    Next
    Set Destination = Destination.Offset(1)
  Next
End Sub

Make sure you change the workbook and worksheet names as appropriate in the
Set statement for the Destination range (as before, the cell address in the
Range statement on that line is the first cell to receive data).

--
Rick (MVP - Excel)







Thank you very much. One last thing can you tell me how to change the
destination variable to another workbook so that the extracted values
go to another worksheet in the workbook named "Sheet1"?

Thanks- Hide quoted text -

- Show quoted text -

That works super. Thank you. Is there any way to also have the macro
paste in the value of the cell in column that is 3 cells before the
selection?

Thanks again.

So if I select cell H2: H300, I would like for it to E2: E300 just
before the other values?

Thanks

 

[Rick Rothstein]

That works super. Thank you. Is there any way to also have

the macro paste in the value of the cell in column that is 3 cells
before the selection?

So if I select cell H2: H300, I would like for it to E2: E300 just
before the other values?

I am not sure what you are asking here. You say "before the other values",
but the way the code works, it puts the split out values in the columns to
the right of each cell in the selection. So if you selected H2:H300, then
the first split out value from each cell would go in E2:E300, the second
split out value from each cell would go in F2:F300, the third split out
value from each cell would go in G2:G300, etc. Where are your values at that
the placement you asked for would be "before" them?

Also, on what sheet are you asking for this to be done... on whatever
worksheet in the workbook with the name "Sheet1" or on the same worksheet
where the selection is made?