Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose code has ceased to transpose
I arcived this code from this forum, I believe. The original data in a single cell was delimited by a / and this code transposed the cell contents from B1 to a list in column A, sans the /. I substituted a comma for the / and it worked just fine on my test cell.
A few minutes ago I tried to use it on comma delimited stuff in cell B1 and now all I get is a long string of commas... ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, etc, in cell B1, nothing in column A. Anybody know what the heck is going on. Excel 2010 and code is in Sheet1 module. Option Explicit Sub SuperTranspose() Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A1").End(xlDown))), ", ") '" / ") End Sub Regards, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose code has ceased to transpose
Howard,
I became a bit confused when trying to follow what the macro is intended to do. It sounds like you want it to take a single string from cell B1 and split it out to its component pieces in column A. The macro you posted, however, does the opposite. It takes the data in column A and concatenates it with a delimiter in cell B1. Thus, with no data in column A, the result of the macro will be a string of commas. After trying out the macro you provided, I reworked it to do the opposite (take a value in cell B1 and transpose it to column A). Both versions are copied below. Hopefully one of them will work for your needs. Option Explicit Sub SuperSplit() 'copies elements in cell B1 into column A Dim vArray As Variant Dim x As Long vArray = Split(Application.Transpose(Range("B1")), ", ") '" / ") For x = 0 To UBound(vArray) Range("A1").Offset(x, 0).Value = vArray(x) Next End Sub Sub SuperJoin() 'Joins column A elements into a single string in cell B1 Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A" & Rows.Count).End(xlUp))), ", ") '" / ") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose code has ceased to transpose
On Tuesday, February 5, 2013 11:50:12 PM UTC-8, Howard wrote:
I arcived this code from this forum, I believe. The original data in a single cell was delimited by a / and this code transposed the cell contents from B1 to a list in column A, sans the /. I substituted a comma for the / and it worked just fine on my test cell. A few minutes ago I tried to use it on comma delimited stuff in cell B1 and now all I get is a long string of commas... ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, etc, in cell B1, nothing in column A. Anybody know what the heck is going on. Excel 2010 and code is in Sheet1 module. Option Explicit Sub SuperTranspose() Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A1").End(xlDown))), ", ") '" / ") End Sub Regards, Howard Hi Ben, You are completely correct, I completely forgot and missread which way the transpose was coded to go. It works just fine when used correctly. Thanks for rewite of the one going the other way, very much appreciated. Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose code has ceased to transpose
Glad to help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose | Excel Worksheet Functions | |||
like a transpose | Excel Programming | |||
Copying with transpose and VB code | Excel Discussion (Misc queries) | |||
Multi Line Transpose Excel VBA code | Excel Programming | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |