Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
List cell values seperated by comma if criteria met Mike Pearson[_2_] Excel Worksheet Functions 2 June 2nd 07 10:31 PM
Find the sum of Comma Seperated Values In a Cell xcelion Excel Worksheet Functions 3 August 24th 05 07:55 AM
column values to a cell with comma seperated Raju Boine. Excel Worksheet Functions 3 July 27th 05 03:30 PM
Reading from comma seperated List Shashi Bhosale Excel Programming 2 May 29th 05 06:50 PM
Problem with comma seperated list Siw H. Thorslund Excel Programming 10 February 24th 04 12:05 PM


All times are GMT +1. The time now is 12:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"