Inexperienced Stackoverflow user, please feel free to point me to a different forum or subforum if needed.
I created the following VBA (then also looked at other code samples online, which all seem to follow the same logic). I test in the debug window and see exactly the result (multi-line string) I expect. However, when I type the formula in a worksheet cell, I get a #Value error.
- The function is spelled correctly (I actually select it in Excel from the formula auto-complete)
- Excel x64 v2108, local install on Win10 x64
Any ideas why this isn't populating back into the cell with the formula?
Function ConcatCells()
'just to be safe, clear the string
ConcatCells = ""
'Loop and add each cell text with a line break
For Each cell In Selection
ConcatCells = ConcatCells & Chr(13) & cell.Text
Next
'remove the leading line break
ConcatCells = Right(ConcatCells, Len(ConcatCells) - 1)
'is the string actually being created correctly? Yes
Debug.Print ConcatCells
End Function
CodePudding user response:
Add Selection As Range
as parameter into your function: Function ConcatCells(Selection As Range)
After this, your function will return a value like this: OneTwoThreeFourFive
When you copy this cell and paste as value,
and after hitting F2
and Enter
, you will have a view like this: