textjoin and filter in VBA

[MMM_84]

Hi there,
What would be the vba code for nested textjoin and filter function to return values into another workbook (not worksheet)? I'd like to assign the macro to "Update" button...
=TEXTJOIN(CHAR(10),TRUE,FILTER($A$2:$A$6&CHAR(10)&$C$2:$C$6&CHAR(10)&$D$2:$D$6,$E$2:$E$6&$B$2:$B$6=$C22&E$21,""))

 

[Muhammad_Usman]

Sub ConcatenateAndFilter()
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim targetWorksheet As Worksheet
    Dim lastRow As Long
    Dim result As String
    Dim i As Long
    
    ' Set the source workbook and worksheets
    Set sourceWorkbook = Workbooks("Workbook1.xlsx") ' Change the filename as needed
    Set sourceWorksheet = sourceWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
    Set targetWorksheet = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name in this workbook as needed

    lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(xlUp).Row
    
    result = ""
    
    For i = 2 To lastRow
        If sourceWorksheet.Cells(i, 5).Value & sourceWorksheet.Cells(i, 2).Value = targetWorksheet.Range("C22").Value & targetWorksheet.Range("E21").Value Then
            result = result & sourceWorksheet.Cells(i, 1).Value & vbNewLine & sourceWorksheet.Cells(i, 3).Value & vbNewLine & sourceWorksheet.Cells(i, 4).Value & vbNewLine
        End If
    Next i
    
    result = Left(result, Len(result) - 1)
    
    ' Display the result in a message box in Workbook 2
    MsgBox result
End Sub

 

[Muhammad_Usman]

In this code, we explicitly set the source workbook and worksheets using the Workbooks and Sheets objects. Make sure to change "Workbook1.xlsx" to the actual filename of Workbook 1 and adjust the sheet names as needed.

You can then run this macro from Workbook 2, Sheet 1 to perform the filtering and concatenation operation on data from Workbook 1, Sheet 1.

 

[MMM_84]

In this code, we explicitly set the source workbook and worksheets using the Workbooks and Sheets objects. Make sure to change "Workbook1.xlsx" to the actual filename of Workbook 1 and adjust the sheet names as needed.

You can then run this macro from Workbook 2, Sheet 1 to perform the filtering and concatenation operation on data from Workbook 1, Sheet 1.

that works perfectly as a message box, what I need is to have a result in a specific cell