This is the code you helped me with the other day. It works great if I have
the code in the workbook I am working on but does not when I have it in my
personal.xls workbook and use it in another workbook. I use
=concatrange(A1:A6) in the helper cell. It returns #NAME? I have values in
cell A1 through A6 in the same worksheet as the helper cell.

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" wrote:

What is "it" that doesn't work?

The macro or the formula?

What does occur?

Maybe posting some code would aid.


Gord Dibben MS Excel MVP

On Fri, 20 Jul 2007 16:56:01 -0700, dford
wrote:

I have macros in my personal.xls workbook that work just fine. I have added a
macro that requires a helper cell to enter a formula. When I enter the
formula in a helper cell in a new workbook, it doesn't work.