Updating Comments Textbox VBA

[RedMan7]

Hello All,

I am having a problem I hope someone can help me with. I currently have three columns, from which I take their contents and combine into a single column (#2). I then used a VBA code to Concatenate each cell in the new column (#2) and have the output come together in one cell (#3).

The module i've created is necessary as it skips any blank values. This worked perfectly, however, when I closed and reopened the excel file, I get a #NAME ? error in the cell (#3). Do you know why this NAME error occurs every time I close and open the file? How can I fix this? I have tried deleting and putting the module again, but it doesn't work. Is there something wrong I am doing?

The formula I use to combine the comments into one column (#2) is this:
=IF(AO23="","","WK"&"."&AC23&"."&" "&AN23&" - "&AO23)

The formula I use in to obtain output in the cell (#3) is this:
=concatenatecells(CH23:CH222)

The module code is below:

Function Concatenatecells(ConcatArea As Range) As String'updateby Extendoffice 20151103
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & Chr(10)): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

 

[RoryA]

Assuming the name of the module is not also ConcatenateCells, did you enable macros when opening the workbook?

 

[ClaireS]

What version of Excel are you using?

 

[ClaireS]

Just that there are a couple of useful new functions in Excel 365 and one I have found really useful is TEXTJOIN. This allows you to concatenate a range of cells WITH a delimiter and WITHOUT including blanks, so you wouldn't need the function module. You could just use:
=TEXTJOIN(CHAR(10),TRUE,CH23:CH222) in cell #3 . You would need to have Wrapping turned on in that cell to see the result properly.

NB the Excel function is CHAR where VBA uses CHR - something I find somewhat annoying!