I have a set of data as below, where the first row for each Reference number is a summary of all rows below it:
| Reference | Date | Comment | Hidden, Concatenated |
|---|---|---|---|
| 1 | 20/6/22 | ||
| 1 | 20/6/22 | Test | 20/6: Test |
| 1 | 18/6/22 | Test2 | 18/6: Test2 |
| 2 | 20/6/22 | ||
| 3 | 16/6/22 |
Where the Hidden, Concatenated column is done using the built-in Excel =CONCAT function, as =CONCAT(TEXT([@[Date]],"dd/mm"),": ",[@Comment]).
Also in this, I have a VBA UserForm in which the Reference is selected from a ComboBox, and Comments added in a TextBox. This inserts a new row directly underneath the summary row, with the date of the note entry and the comment populated into this new row.
I have used the UDF provided in https://www.extendoffice.com/documents/excel/2723-excel-concatenate-based-on-criteria.html, which works well, but takes a long time, as this is a long document.
The end aim is for the cell C2 to equal 20/6: Test, 18/6: Test2
I have been using the UDF:
ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ", ") As Variant
as =ConcatenateIf(A:A,[@[Reference]],D:D) in C2.
I have done a workaround now of limiting the range to A2:A500 and D2:D500, but is it possible to call this function within the sub that inserts the new line? I.e. Not to insert the function as ws.Cells("C2").Formula = "=ConcatenateIf(A:A,[@[Reference]],D:D, but to have the Value of the function inserted?
Thank you in advance.