Code to insert a coma

  • Thread starter Thread starter Howard
  • Start date Start date
H

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

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.
Click to expand...

This can be longer or shorter but the format stays the same for the
single letters & numbers following the word in D1 ("Abit" here).
 
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,



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 :




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

single letters & numbers following the word in D1 ("Abit" here).
Click to expand...
Hi isabelle

Your code is spot-on! I did make this small change to do a coma instead odan 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 onmy 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
 
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
Click to expand...

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
 
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.
Click to expand...
Abit A 9 B 8 C 7 D 6 E 5
Click to expand...



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
Click to expand...

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,
Click to expand...

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


Regards
Claus Busch
 
Hi Howard,



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






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
Click to expand...

Yes, thanks again.
Howard
 
Hi Howard,



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






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
Click to expand...

Like this, can be much longer and even shorter.

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

in D1.

Howard
 
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
Click to expand...

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
 
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
Click to expand...



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
Click to expand...

Excellent! Thanks Claus.

Tegards,
Howard
 
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)
Click to expand...

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
 
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
Click to expand...
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "
Next
Click to expand...

[D1] = Left([D1], Len([D1]) - 2)
Click to expand...



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
Click to expand...
 
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
Click to expand...
[D1] = [D1] & " " & Cells(1, i) & " " & Cells(1, i + 1) & ", "
Next
Click to expand...

[D1] = Left([D1], Len([D1]) - 2)
Click to expand...



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
Click to expand...

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
 
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.
Click to expand...

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
 
Howard formulated on Sunday :









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
Click to expand...

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
 
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
Click to expand...

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 shownbelow. 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 intoone 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 doesvirtually the same thing with two columns but was not able to convert it to handle three.

HTH
Howard
 
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.
Click to expand...

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


Regards
Claus Busch
 
Back
Top