VBA assistance to modify code

[tyantorno]

Hello All,

I have a piece of code that almost works for what I need it to do.
Basically, I have data consisting of a variable number of column separated
strings. example

01AL,02AL,03AL,04AL

When I run the code below

Sub CommaSeparated()

Dim curr_range As Range
Dim Row As Range
Dim arr As Variant
Dim cell As Variant
Dim output_str As String
Dim output_arr As Variant

Set curr_range = ActiveSheet.Range("A1:A9999")
For Each Row In curr_range
arr = Split(Row, ",")
For Each cell In arr
output_str = output_str & "," & cell

Next cell

Next Row
output_str = Replace(output_str, " ", "")
output_str = Right(output_str, Len(output_str) - 1)
output_arr = Split(output_str, ",")

ActiveSheet.Range("A:A").Value = Application.WorksheetFunction.Transpose(output_arr)

End Sub

It gives me sixteen rows of:

01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL

and a #VALUE down the rest of the column

I was hoping to only get four rows of:

01AL
02AL
03AL
04AL

with no #VALUE down rest of column.

Thank you in advance for any assistance/suggestions.:banghead:

 

[Rx_]

Is your orginal data in tables (or a query)?
http://www.btabdevelopment.com/ts/export2ExcelNewNamedSheet
Try to do as much work in an Access Query as possible.
Then use the query as part of creating a record set object.
Move the data in the recordset object over to an Excel object.
Use Excel objects to tidy up, format, and dressup the results.

The link above is a very good site for you to get started.
If you use one of the code samples from the link listed above - it will probably narrow your question to get exactly what you want.

 

[tyantorno]

Thank you so much RX, i will surely look into that link. Have a great Thanksgiving. Tom