Updating Comments Textbox VBA

RedMan7

New Member
Joined
Feb 15, 2019
Messages
20
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:

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Assuming the name of the module is not also ConcatenateCells, did you enable macros when opening the workbook?
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top