Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
Want to xpose a list of items in A to cells in B twenty at a time then the next twentyin B below the first etc. Thanks, Howard Option Explicit Sub SuperJoin() Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A" & Rows.Count).End(xlUp))), " , ") '" / ") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
hi Howard,
Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20 x = x + 1 Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i), Range("A" & i + 19))), ",") Next End Sub isabelle Le 2013-09-04 22:22, Howard a écrit : Want to xpose a list of items in A to cells in B twenty at a time then the next twentyin B below the first etc. Thanks, Howard Option Explicit Sub SuperJoin() Range("B1") = Join(Application.Transpose(Range(Range("A1"), _ Range("A" & Rows.Count).End(xlUp))), " , ") '" / ") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
sorry it was missing a space at the last argument of Join function
Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20 x = x + 1 Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i), Range("A" & i + 19))), " ,") Next End Sub isabelle Le 2013-09-04 23:28, isabelle a écrit : hi Howard, Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20 x = x + 1 Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i), Range("A" & i + 19))), ",") Next End Sub isabelle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
On Wednesday, September 4, 2013 8:52:58 PM UTC-7, isabelle wrote:
sorry it was missing a space at the last argument of Join function Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp) Step 20 x = x + 1 Range("B" & x) = Join(Application.Transpose(Range(Range("A" & i), Range("A" & i + 19))), " ,") Next End Sub isabelle Very nice. Thank you isabelle. Regards, Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
Hi isabelle,
My first response was to a test of 1 to 100 in col A. Worked just fine, 1 to 100 - 5 rows, 1-20, 21-40, 41-60, 61-80, 81-100. I did some more tests with these results which puzzle me. 10 to 109 - 5 rows ok and 1 row with 19 commas 100 to 199 - 5 rows ok and 5 rows of 19 commas a1 to a100 Type mismatch error. Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
Hi Howrd,
Am Wed, 4 Sep 2013 21:39:35 -0700 (PDT) schrieb Howard: 10 to 109 - 5 rows ok and 1 row with 19 commas 100 to 199 - 5 rows ok and 5 rows of 19 commas a1 to a100 Type mismatch error. try: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose(Range(Cells(i, 1), _ Cells(i + .Min(19, .CountA(Range(Cells(i, 1), _ Cells(i + 19, 1))) - 1), 1))), ",") End With Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
try: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose(Range(Cells(i, 1), _ Cells(i + .Min(19, .CountA(Range(Cells(i, 1), _ Cells(i + 19, 1))) - 1), 1))), ",") End With Next End Sub Regards Claus B. Hi Claus, With: 10 to 109 - works fine. a1 to a100 - works fine. 100 strings like these two - works fine. MAE511363 XEL551995 With this 100 to 199 I get five Scientific Notations like this 1.00101102103104E+59 I be way stumped. I checked column B cell format and nothing is selected. I tried this at the end Columns("B:B").Select Selection.NumberFormat = "General" and it did not fix anything. Regards, Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
Hi Howard,
Am Wed, 4 Sep 2013 23:00:52 -0700 (PDT) schrieb Howard: 100 strings like these two - works fine. MAE511363 XEL551995 With this 100 to 199 I get five Scientific Notations like this 1.00101102103104E+59 first, you don't need the MIN The comma is the separator for the thousands and so you will get a very great number. Try semicolon as separator if you have numbers in your range: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose( _ Range(Cells(i, 1), Cells(i + .CountA( _ Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ";") End With Next End Sub or comma with a following space: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose( _ Range(Cells(i, 1), Cells(i + .CountA( _ Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ", ") End With Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xpose twenty rows then the next twent etc.
first, you don't need the MIN The comma is the separator for the thousands and so you will get a very great number. Try semicolon as separator if you have numbers in your range: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose( _ Range(Cells(i, 1), Cells(i + .CountA( _ Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ";") End With Next End Sub or comma with a following space: Sub SuperJoin() Dim i As Long, x As Long For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 20 x = x + 1 With WorksheetFunction Range("B" & x) = Join(Application.Transpose( _ Range(Cells(i, 1), Cells(i + .CountA( _ Range(Cells(i, 1), Cells(i + 19, 1))) - 1, 1))), ", ") End With Next End Sub Regards Claus B. Thanks Claus, that cleared it up very nicely. All works excellent. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
120= rupees one hundred twenty only | Excel Worksheet Functions | |||
Patek Philippe Twenty~4 Watch, Best Wristwatch | Excel Discussion (Misc queries) | |||
creating a table from twenty worksheets | Excel Programming | |||
how do i convert numbers (25) into words (Twenty Five Only) ... | Excel Worksheet Functions | |||
How do I convert number 123 to text One Hundred and Twenty Three? | Excel Discussion (Misc queries) |