Colin
The green part in the line is caused by the two single quotes I put in telling
Excel to ignore that part.
Another poster did not want the commas so I remmed that part out and sent you
the same code by mistake.
You don't want that part ignored, so remove them to this.
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
That is not the cause of the #NAME? error however.
That error usually comes from Excel not recognizing the function name.
Did you Copy/paste to a General Module in the same workbook?
If you click on the Fx button and select User Defined from the list, does
ConCatRange show up?
Gord
On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes
wrote:
HI Gord
OK I've entered and saved this function into the code in my workbook :
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
and entered the formula
=ConCatRange(A1:A20) in B1
I have my numbers down the sheet in column A from A1 to A20.
I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?
The VBA editor does seem to be giving an error in this phrase :
If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","
It goes green after the word 'text'. If i change any part of this I get
red errors....
Thanks again.
Best Wishes
Colin
In article , Colin Hayes
writes
Hi Gord
OK Thanks for that. Very helpful.
Best Wishes
Colin
In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Colin
Apologies for the misunderstanding.
I saw a post from you asking Toppers for instructions on how to implement the
Function and my assumer got stuck.
If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the ConCatRange function code in there.
Save the workbook and hit ALT + Q to return to your workbook.
Enter the formula =ConCatRange(A1:A20) in B1
Gord
On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes
wrote:
In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Your choice.
Have fun.
Gord
HI Gord
No , not my choice at all. Quite the opposite in fact. I spent some time
trying to implement the code , and wasn't able to.
I'm grateful for any advice given of course , but we're not all experts.
MVPs have a role as instructor , don't they?
Best Wishes
Colin
On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
wrote:
HI All
I'm pleased you were able to sort that out between yourselves...!
I'll do it manually ....
^_^
Best Wishes
Colin
In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Toppers
One problem with this.
If a cell in the range is blank it returns a 0
So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
This function ignores blanks.
Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
Gord Dibben MS Excel MVP
On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
of
t.
co
m
wrote:
Try this macro:
Put this required cell;
=onelist(A1:A100) .
.. set range as required
Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function
"Colin Hayes" wrote:
Hi All
Hope someone can help.
I have a column of numbers going down the sheet in column A.
I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.
I've been playing around for ages to do this - can someone put me out
of
my misery?
Best Wishes
Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP