Format pasted rows within userforum-textbox into concatenation or borderline?

Question

I get a mismatch error in this line :

row_str = Join(cell_rng, Chr(10))

Thank you. I am intermediate.

I attached a piece of the code below:

    Dim last_row As String
    Dim last_col As String
    Dim office_str As String
    Dim lookupVal As String
    Dim i As Long
    Dim seperate_cells, cell_rng As Range
    Dim r As Range
    Dim row_str As String        


With Contacts
    For i = 2 To last_row
        Set cell_rng = Rows(i & ":" & i + 1)
        For Each r In cell_rng.Rows
           seperate_cells = cellsSeparator(r.SpecialCells(xlCellTypeConstants))
           If row_str = "" Then
            row_str = Join(cell_rng, Chr(10))
           Else
            row_str = row_str & vbLf & Join(cell_rng, Chr(10))
           End If
        Next
        Debug.Print row_str
        Client_Finder.result.Text = Client_Finder.result.Text & vbLf & row_str
    Next i       
End With
````

Answer 1

Please try the next way. It will place the values of the necessary specific row in the text box, each value separated by " | ":

Sub testSeparatorsBetweenRowCells()
 'your existing code...
 Dim arr, rngR As Range
  For i = 2 To last_row
            lookupVal = cells(i, office_str)
            ' Compare ComboBox with the range from the spreadsheet
            If lookupVal = Office_Code Then
                Set rngR = rows(i & ":" & i).SpecialCells(xlCellTypeConstants) 'Set a range which will return all cells value in the row, except the empty ones
                arr = arrCells(rngR)  'call a function able to make an array from the range set in the above line
                Client_Finder.result.Text = Client_Finder.result.Text & vbLf & Join(arr, " | ") 'add the text obtained by joining the array to the next line of existing text
            End If
    Next i
End Sub

Function arrCells(rng As Range) As Variant
   Dim arr, Ar As Range, i As Long, C As Range
   ReDim arr(rng.cells.count - 1) 'ReDim the array to be filled as the range cells number.
                                           '- 1, because the array is 0 based...
   For Each Ar In rng.Areas       'iterate between the range areas
        For Each C In Ar.cells      'iterate between cells of each area
            arr(i) = C.value: i = i + 1 'put each cell value in the array
        Next
   Next
   arrCells = arr                      'make the function returning the arr
End Function

If the text in the text box still goes on the next line, try making the text box property WordWrap False. If you cannot see all the text, make the textbox wider or decrease its font size.

Please, test it and send some feedback.

Edited: Please, try understanding the next piece of code, able to deal with copying more rows at once:

Sub testCopyingMoreRows()
   Dim sh As Worksheet, i As Long, rng As Range, r As Range, arr, strRow As String
   
   Set sh = ActiveSheet
   i = 9
   Set rng = sh.rows(i & ":" & i + 1)
   'you ca select cells, rows (even not consecutive) and use:
   'Set rng = Selection.EntireRow 'just uncomment this code line...
   'extract rows and paste their contents (exept the empty cells) in Imediate Window
   For Each r In rng.rows
        arr = arrCells(r.SpecialCells(xlCellTypeConstants))
        If strRow = "" Then
            strRow = Join(arr, " | ")
        Else
            strRow = strRow & vbLf & Join(arr, " | ")
        End If
   Next
   Debug.Print strRow
   'instead returning in Imediate Window, you can do it in your text box (uncomment the next line):
   'Client_Finder.result.Text = Client_Finder.result.Text & vbLf & strRow
End Sub

The code uses the same function arrCells...