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 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

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

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

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

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

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

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

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

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

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

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