Code to insert a coma
 

I run a macro and it puts these values in cells D1 to N1. This can be longer or shorter but the format stays the same for the single letters & numbers following the word in D1 ("Abit" here).

Abit A 9 B 8 C 7 D 6 E 5

Then thecode below is called and I get this ALL IN ONE CELL, D1.
Abit A 9 B 8 C 7 D 6 E 5

Sub ConIt()
Dim i As Integer
i = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 3
For i = 1 To i
Range("D1").Value = Range("D1").Value & " " & Range("D1").Offset(0, i)
Next
End Sub

How can I make the code put a coma after each number EXCEPT the last number which is 5 here.

Thanks,
Howard

Code to insert a coma
 

hi Howard,

Sub ConIt()
Dim i As Integer, y As Integer, a As String
y = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 4
For i = 1 To y
If IsNumeric(Range("D1").Offset(0, i)) And i < y Then a = "'" Else a
= ""
Range("D1").Value = Range("D1").Value & " " & Range("D1").Offset(0,
i) & a
Next
End Sub


isabelle

Le 2013-01-12 23:40, Howard a écrit :
I run a macro and it puts these values in cells D1 to N1.

This can be longer or shorter but the format stays the same for the
single letters & numbers following the word in D1 ("Abit" here).

Abit A 9 B 8 C 7 D 6 E 5

Then thecode below is called and I get this ALL IN ONE CELL, D1.
Abit A 9 B 8 C 7 D 6 E 5

Sub ConIt()
Dim i As Integer
i = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 3
For i = 1 To i
Range("D1").Value = Range("D1").Value & " " & Range("D1").Offset(0, i)
Next
End Sub

How can I make the code put a coma after each number EXCEPT the last number which is 5 here.

Thanks,
Howard

Code to insert a coma
 

Another way...

Sub DelimitData()
Dim vData, i%, vDataOut()
vData = Range("D1:N1"): ReDim vDataOut(1 To UBound(vData, 2))
For i = 1 To UBound(vData, 2): vDataOut(i) = vData(1, i): Next
With Range("D1:N1")
.ClearContents: .Cells(1) = Join(vDataOut, ", ")
End With
End Sub

...which assumes you want to clear the other columns. With very little
thought, you could modify the macro that puts those values there to
skip writing to each column and put the result directly into the target
cell.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to insert a coma
 

On Saturday, January 12, 2013 9:46:06 PM UTC-8, isabelle wrote:
hi Howard,



Sub ConIt()

Dim i As Integer, y As Integer, a As String

y = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 4

For i = 1 To y

If IsNumeric(Range("D1").Offset(0, i)) And i < y Then a = "'" Else a

= ""

Range("D1").Value = Range("D1").Value & " " & Range("D1").Offset(0,

i) & a

Next

End Sub





isabelle



Le 2013-01-12 23:40, Howard a écrit :

I run a macro and it puts these values in cells D1 to N1.



This can be longer or shorter but the format stays the same for the

single letters & numbers following the word in D1 ("Abit" here).



Abit A 9 B 8 C 7 D 6 E 5



Then thecode below is called and I get this ALL IN ONE CELL, D1.

Abit A 9 B 8 C 7 D 6 E 5



Sub ConIt()

Dim i As Integer

i = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 3

For i = 1 To i

Range("D1").Value = Range("D1").Value & " " & Range("D1").Offset(0, i)

Next

End Sub



How can I make the code put a coma after each number EXCEPT the last number which is 5 here.



Thanks,

Howard


Hi isabelle

Your code is spot-on! I did make this small change to do a coma instead od an apostphre..."Then a = "'" Else a" to "Then a = "," Else a". I bet you did that on purpose just to see if I was paying attention...LOL.
Thanks isabelle, I really appreciate it.

Regards,
Howard

And Gary, thanks for your effort. Your code puts a coma after each character except the last and I need a coma after each number except the last. I still appreciate your time and effort.

Your comment... <you could modify the macro that puts those values there to
skip writing to each column and put the result directly into the target
cell. was exactly what I sat out to do in the first place and fell flat on my face. I tinkered around and got to where I am now and asked for help from that point.

I'd be glad to see some code to eliminate the need to go to columns in the first place and go directly to the target with results like this:

Abit A 9, B 8, C 7, D 6, E 5

in D1.

Thanks again.

Regards,
Howard

Code to insert a coma
 

Hi Howard,

Am Sat, 12 Jan 2013 20:40:57 -0800 (PST) schrieb Howard:

I run a macro and it puts these values in cells D1 to N1. This can be longer or shorter but the format stays the same for the single letters & numbers following the word in D1 ("Abit" here).

Abit A 9 B 8 C 7 D 6 E 5

Then thecode below is called and I get this ALL IN ONE CELL, D1.
Abit A 9 B 8 C 7 D 6 E 5

and another solution:

Sub Test()
Dim LCol As Integer
Dim i As Integer

LCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 5 To LCol
[D1] = IIf(IsNumeric(Cells(1, i)), [D1] & " " & Cells(1, i), _
[D1] & ", " & Cells(1, i))
Next
[D1] = Trim([D1])

End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 2:00:00 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sat, 12 Jan 2013 20:40:57 -0800 (PST) schrieb Howard:



I run a macro and it puts these values in cells D1 to N1. This can be longer or shorter but the format stays the same for the single letters & numbers following the word in D1 ("Abit" here).



Abit A 9 B 8 C 7 D 6 E 5



Then thecode below is called and I get this ALL IN ONE CELL, D1.

Abit A 9 B 8 C 7 D 6 E 5



and another solution:



Sub Test()

Dim LCol As Integer

Dim i As Integer



LCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 5 To LCol

[D1] = IIf(IsNumeric(Cells(1, i)), [D1] & " " & Cells(1, i), _

[D1] & ", " & Cells(1, i))

Next

[D1] = Trim([D1])



End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus, always good to hear from you. Any way to remove that stray , between Abit & B 25,?

Abit , B 25, C 24,

Thanks.
Howard

Code to insert a coma
 

Hi Howard,

Am Sun, 13 Jan 2013 02:14:38 -0800 (PST) schrieb Howard:

Any way to remove that stray , between Abit & B 25,?

Abit , B 25, C 24,

sorry, I missunderstood your problem. But you still have a good solution
from Isabelle.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 2:27:48 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sun, 13 Jan 2013 02:14:38 -0800 (PST) schrieb Howard:



Any way to remove that stray , between Abit & B 25,?



Abit , B 25, C 24,



sorry, I missunderstood your problem. But you still have a good solution

from Isabelle.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Yes, thanks again.
Howard

Code to insert a coma
 

Hi Howard,

Am Sun, 13 Jan 2013 02:37:59 -0800 (PST) schrieb Howard:

Yes

please post here how the solution shall look like


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 4:09:03 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sun, 13 Jan 2013 02:37:59 -0800 (PST) schrieb Howard:



Yes



please post here how the solution shall look like





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Like this, can be much longer and even shorter.

Abit A 9, B 8, C 7, D 6, E 5

in D1.

Howard

Code to insert a coma
 

Hi Howard,

Am Sun, 13 Jan 2013 04:27:49 -0800 (PST) schrieb Howard:

Abit A 9, B 8, C 7, D 6, E 5

then try:

Sub Test()
Dim i As Integer
Dim LCol As Integer

For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "
Next
[D1] = Left([D1], Len([D1]) - 2)
End Sub



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 4:41:34 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sun, 13 Jan 2013 04:27:49 -0800 (PST) schrieb Howard:



Abit A 9, B 8, C 7, D 6, E 5



then try:



Sub Test()

Dim i As Integer

Dim LCol As Integer



For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "

Next

[D1] = Left([D1], Len([D1]) - 2)

End Sub







Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Excellent! Thanks Claus.

Tegards,
Howard

Code to insert a coma
 

Hi Howard,

Am Sun, 13 Jan 2013 13:41:34 +0100 schrieb Claus Busch:

For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "
Next
[D1] = Left([D1], Len([D1]) - 2)

there are two space between the parts. Try:

Sub Test()
Dim i As Integer
Dim LCol As Integer

For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ","
Next
[D1] = Left([D1], Len([D1]) - 1)
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 11:18:27 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sun, 13 Jan 2013 13:41:34 +0100 schrieb Claus Busch:



For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "

Next

[D1] = Left([D1], Len([D1]) - 2)



there are two space between the parts. Try:



Sub Test()

Dim i As Integer

Dim LCol As Integer



For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ","

Next

[D1] = Left([D1], Len([D1]) - 1)

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

On Sunday, January 13, 2013 11:18:27 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sun, 13 Jan 2013 13:41:34 +0100 schrieb Claus Busch:



For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "

Next

[D1] = Left([D1], Len([D1]) - 2)



there are two space between the parts. Try:



Sub Test()

Dim i As Integer

Dim LCol As Integer



For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ","

Next

[D1] = Left([D1], Len([D1]) - 1)

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I tried the -1 in place of the -2 and the only difference I could detect in the outcome was a coma at the very end of the string.

The two spaces are not a problem, I had not even noticed. I'll probably stay with the two spaces and no coma at the end.

I appreciate your diligence.

Regards,
Howard

Code to insert a coma
 

Howard formulated on Sunday :
Your comment... <you could modify the macro that puts those values there to
skip writing to each column and put the result directly into the target
cell. was exactly what I sat out to do in the first place and fell flat on
my face. I tinkered around and got to where I am now and asked for help from
that point.

I'd be glad to see some code to eliminate the need to go to columns in the
first place and go directly to the target with results like this:

Abit A 9, B 8, C 7, D 6, E 5

in D1.

Howard, show me the code you're using to put the values into colums so
I can see how to modify it to output directly to D1.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to insert a coma
 

On Sunday, January 13, 2013 2:48:39 PM UTC-8, GS wrote:
Howard formulated on Sunday :

Your comment... <you could modify the macro that puts those values there to

skip writing to each column and put the result directly into the target

cell. was exactly what I sat out to do in the first place and fell flat on

my face. I tinkered around and got to where I am now and asked for help from

that point.



I'd be glad to see some code to eliminate the need to go to columns in the

first place and go directly to the target with results like this:



Abit A 9, B 8, C 7, D 6, E 5



in D1.



Howard, show me the code you're using to put the values into colums so

I can see how to modify it to output directly to D1.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi Garry,
This is it. The TComa call is code by Claus (renamed & slightly modified by me) that re-gathers the column data into one cell, D1.

Thanks for taking a look.

Regards,
Howard

Option Explicit

Sub TShirt()
Dim i As String
Dim c As Range
Dim Rng As Range

i = Range("D1").Value
Set Rng = Range("A5:A" & Range("A" & Rows.Count).End(xlUp).Row)

For Each c In Rng
If c.Value = i Then
c.Offset(0, 1).Resize(1, 2).Copy Range("Z1").End(xlToLeft).Offset(0, 1)
End If
Next
TComa
End Sub

'Claus Busch
Sub TComa()
Dim i As Integer
Dim Y As Integer
Dim LCol As Integer

For i = 5 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "
Next
[D1] = Left([D1], Len([D1]) - 2)

Y = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 3
Range("D1").Copy Range("E1000").End(xlUp).Offset(1, 0)
Range("D1").Resize(1, Y).ClearContents
Range("D1").Select
End Sub

Code to insert a coma
 

I was asking for the code that collects the values from the *source*,
or creates the values by whatever means, and put these in cols D:N. If
I read your code correctly, the values are stored/retrieved from cols
A:B. Is this correct? If so, what's the layout for the source values in
cols A:B?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to insert a coma
 

On Sunday, January 13, 2013 8:01:49 PM UTC-8, GS wrote:
I was asking for the code that collects the values from the *source*,

or creates the values by whatever means, and put these in cols D:N. If

I read your code correctly, the values are stored/retrieved from cols

A:B. Is this correct? If so, what's the layout for the source values in

cols A:B?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi Garry,
I was responding to this query in another forum to wit:

======================
The difficult ones are the T-shirts, as while there ID remain the same, they are different based on size. So, I need to merge together the cells shown below. In result is a receipt that I can send to them.

ID Size Quantity
SMSFTS1 Large 1
SMSFTS1 Medium 1
SMSFTS1 Small 2
SMSFTS1 X Large 1
SMSFTS1 XX Large 1
SMSFTS2 Large 1
SMSFTS2 Medium 1
SMSFTS2 Small 1
SMSFTS2 X Large 1

Here is an example of what I need it to look.

SMSFTS1 Large 1, Medium 1, Small 2, X Large 1, XX Large 1
SMSFTS2 Large 1, Medium 1, Small 1, X Large
================

So I copied this data to columns A, B, C in my worksheet and wrote the TShirt() sub which moves the data to D1 -- ?1 (depending how many sizes are involved, note the difference in lenght of the out comes of SMSFTS1 vs SMSFTS2)

As you can see I call Claus's TComa () sub to consolidat the data back into one cell with the comas.

At first I thought I was going to go from the three columns of ID, Size, Quantity straight to a single cell with the desired out come, but found that was indeed beyond my paygrade in Excel. I have seen similar code that does virtually the same thing with two columns but was not able to convert it to handle three.

HTH
Howard

Code to insert a coma
 

Hi Howard,

Am Sun, 13 Jan 2013 14:22:16 -0800 (PST) schrieb Howard:

I tried the -1 in place of the -2 and the only difference I could detect in the outcome was a coma at the very end of the string.

The two spaces are not a problem, I had not even noticed. I'll probably stay with the two spaces and no coma at the end.

[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ","
.................................................. ..........................................^^^^^^
I deleted the space behind the comma too.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

Where does the data come from? Is it already in a 3 col worksheet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to insert a coma
 

On Monday, January 14, 2013 7:31:06 AM UTC-8, GS wrote:
Where does the data come from? Is it already in a 3 col worksheet?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

The OP offered it up as an example of what he was dealing with and what the results should look like. How it came to his worksheet is unknown to me. I sorta get the feeling it is some kind of data dump to his worksheet that includes a lot of other merchendise "stuff" and the T-Shirts are the troublesome part for the OP. He offered up three columns and I just took it from there.

No screen shot, just three columns of t-shirt info and a munually typed in result he hoped to achieve.

Howard

Code to insert a coma
 

On Monday, January 14, 2013 7:31:06 AM UTC-8, GS wrote:
Where does the data come from? Is it already in a 3 col worksheet?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Garry, Here is the complete OP query.

==========
Multiple text cells merged into single cell

Hi everybody- this is my first time, and I hope it's an easy solution.

I have multiple cells they need to be combined into one cell. I have read all of the threads here, and I just can't match it up. I don't need a proof solution, just a way to get started.

My cells are based on sales of products. The number of cells received change every time a sale is made. The difficult ones are the T-shirts, as while there ID remain the same, they are different based on size. So, I need to merge together the cells shown below. In result is a receipt that I can send to them.

ID Size Quantity
SMSFTS1 Large 1
SMSFTS1 Medium 1
SMSFTS1 Small 2
SMSFTS1 X Large 1
SMSFTS1 XX Large 1
SMSFTS2 Large 1
SMSFTS2 Medium 1
SMSFTS2 Small 1
SMSFTS2 X Large 1

Here is an example of what I need it to look.

SMSFTS1 Large 1, Medium 1, Small 2, X Large 1, XX Large 1
SMSFTS2 Large 1, Medium 1, Small 1, X Large

As I'm sure you understand, this constantly changes.

I have tried to use CONCATENATE, I think it has to be manual.

If you can give me any guidance I would surely appreciate it.[/quote]
==========

Howard

Code to insert a coma
 

Hi Howard,

Am Mon, 14 Jan 2013 11:06:37 -0800 (PST) schrieb Howard:

ID Size Quantity
SMSFTS1 Large 1
SMSFTS1 Medium 1
SMSFTS1 Small 2
SMSFTS1 X Large 1
SMSFTS1 XX Large 1
SMSFTS2 Large 1
SMSFTS2 Medium 1
SMSFTS2 Small 1
SMSFTS2 X Large 1

Here is an example of what I need it to look.

SMSFTS1 Large 1, Medium 1, Small 2, X Large 1, XX Large 1
SMSFTS2 Large 1, Medium 1, Small 1, X Large

why don't you try a PivotTable?
Another look but you see all important things with one look


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

Hi Howard,

Am Mon, 14 Jan 2013 11:06:37 -0800 (PST) schrieb Howard:

ID Size Quantity
SMSFTS1 Large 1
SMSFTS1 Medium 1
SMSFTS1 Small 2
SMSFTS1 X Large 1
SMSFTS1 XX Large 1
SMSFTS2 Large 1
SMSFTS2 Medium 1
SMSFTS2 Small 1
SMSFTS2 X Large 1

Here is an example of what I need it to look.

SMSFTS1 Large 1, Medium 1, Small 2, X Large 1, XX Large 1
SMSFTS2 Large 1, Medium 1, Small 1, X Large

please download the workbook "Howard" from:
https://skydrive.live.com/#cid=9378A...121822A3%21191
There is a solution with PivotTable with dynamic range name and a
solution with VBA


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to insert a coma
 

Ok, so if the list is 3 cols that need to be consolidated by product
ID, then SMSFTS1 occupies A1:A5 and so what do you want to do with
D2:D5? Same for the next ID and so on...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Code to insert a coma
 

GS explained on 14/01/2013 :
Ok, so if the list is 3 cols that need to be consolidated by product ID, then
SMSFTS1 occupies A1:A5 and so what do you want to do with D2:D5? Same for the
next ID and so on...

Sorry.., I forgot there were headers and so increment rows by 1!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion