Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Eachcell in Range tranpose comma seperated values to a list in D
This works great for cell A1 to rows in D. How do I modify the code to do A1:Ax into column D down as far as needed? My "Error 400" code attempt at it follows this working code. Thanks. Howard Option Explicit Sub SuperSplit() Dim vArray As Variant Dim x As Long vArray = Split(Application.Transpose(Range("A1")), ", ") '" / ") For x = 0 To UBound(vArray) Range("D1").Offset(x, 0).Value = vArray(x) Next End Sub Not working: Sub SuperSplitX() Dim vArray As Variant Dim x As Long Dim c As Range Dim y As Long y = 1 For Each c In Range("A1:A3") vArray = Split(Application.Transpose(Range("A:" & y)), ", ") '" / ") y = y + 1 For x = 0 To UBound(vArray) Range("D1").End(xlUp).Offset(x, 0).Value = vArray(x) Next Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Eachcell in Range tranpose comma seperated values to a list in D
Hi Howard,
Am Sun, 13 Oct 2013 14:40:03 -0700 (PDT) schrieb Howard: This works great for cell A1 to rows in D. How do I modify the code to do A1:Ax into column D down as far as needed? try: Sub Test() Dim varOut As Variant Dim rngC As Range Dim FECell As Range Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In Range("A1:A" & LRow) varOut = Split(rngC, ", ") Set FECell = Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) FECell.Resize(rowsize:=(UBound(varOut) + 1)) = _ Application.Transpose(varOut) Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Eachcell in Range tranpose comma seperated values to a list in D
Hi again,
Am Mon, 14 Oct 2013 00:06:32 +0200 schrieb Claus Busch: This works great for cell A1 to rows in D. How do I modify the code to do A1:Ax into column D down as far as needed? or write all cells in one string and then transpose: Sub Test2() Dim varOut As Variant Dim rngC As Range Dim FECell As Range Dim LRow As Long Dim myString As String LRow = Cells(Rows.Count, 1).End(xlUp).Row For Each rngC In Range("A1:A" & LRow) myString = myString & rngC & ", " Next myString = Left(myString, Len(myString) - 2) varOut = Split(myString, ", ") Set FECell = Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) FECell.Resize(rowsize:=(UBound(varOut) + 1)) = _ Application.Transpose(varOut) End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Eachcell in Range tranpose comma seperated values to a list in D
Hi Howard,
Am Mon, 14 Oct 2013 00:11:32 +0200 schrieb Claus Busch: or write all cells in one string and then transpose: the second suggestions is faster. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Eachcell in Range tranpose comma seperated values to a listin D
On Sunday, October 13, 2013 3:14:58 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 14 Oct 2013 00:11:32 +0200 schrieb Claus Busch: or write all cells in one string and then transpose: the second suggestions is faster. Regards Claus B. Both work well! I will use your two and the working one I submitted for some good study material for me. Thanks, Claus. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List cell values seperated by comma if criteria met | Excel Worksheet Functions | |||
Find the sum of Comma Seperated Values In a Cell | Excel Worksheet Functions | |||
column values to a cell with comma seperated | Excel Worksheet Functions | |||
Reading from comma seperated List | Excel Programming | |||
Problem with comma seperated list | Excel Programming |