Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
May 18, 2022
Solved

Is there a way to have a dynamic textjoin with IF condition lookup from another workbook VBA

I tried to create a vba code for this but was unsuccessful, hopefully i could get some help from here.

 

Sub join()

Dim ws As Worksheet
Dim exportWb As Workbook
Dim exportWs As Worksheet
Set exportWs = exportWb.Sheets("Sheet1")
Set ws = Sheet1

Workbooks.Open ("C:\Users\desktop\export.xlsx")
Set exportWb = ActiveWorkbook
ThisWorkbook.Activate

Dim exportlastRow1 As Range
Dim exportlastRow2 As Range

exportlastRow1 = exportWs.Cells(exportWs.Rows.Count, "A").End(xlUp).Row
exportlastRow2 = exportWs.Cells(exportWs.Rows.Count, "E").End(xlUp).Row

ws.Range("C10") = ActiveSheet.Evaluate("TEXTJOIN(", ",TRUE,IF(C15 = 
exportlastRow2,exportlastRow1,""))")

End Sub

 

 

 

 

 

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 19, 2022

    hrh_dash 

    Sorry

    Sub join()
    Dim ws As Worksheet
    Dim exportWb As Workbook
    Dim exportWs As Worksheet
    Dim exportlastRow As Long
    Set ws = Sheet1
    Set exportWb = Workbooks.Open("C:\Users\desktop\export.xlsx")
    Set exportWs = exportWb.Sheets("Sheet1")
    exportlastRow = exportWs.Cells(exportWs.Rows.Count, "E").End(xlUp).Row
    ThisWorkbook.Activate
    ws.Range("C10") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF([export.XLSX]Sheet1!E2:E" & _
        exportlastRow & "=C15,[export.XLSX]Sheet1!A2:A" & exportlastRow & ",""""))")
End Sub

49 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 18, 2022

    hrh_dash 

    You use exportWb before you have assigned a value to it. This can easily be corrected by moving down the line

    Set exportWs = exportWb.Sheets("Sheet1")

     

    But the 3rd argument of TEXTJOIN makes no sense:

    IF(C15=exportlastRow2,exportlastRow1,"")

    does not evaluate to anything that can be concatenated.

    (And the use of quotes is wrong, but that's for later)

    What are you trying to do?

     

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      May 19, 2022

      HansVogelaar , previously i will using this formula below and now i would like it to be executing via macro.

       

      TEXTJOIN(", ",TRUE,IF($C$15=[export.XLSX]Sheet1!$E$2:$E$13047,[export.XLSX]Sheet1!$A$2:$A$13047,""))

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 19, 2022

        hrh_dash 

        Try this:

        Sub join()
    Dim ws As Worksheet
    Dim exportWb As Workbook
    Dim exportWs As Worksheet
    Dim exportlastRow As Range
    Set ws = Sheet1
    Set exportWb = Workbooks.Open("C:\Users\desktop\export.xlsx")
    Set exportWs = exportWb.Sheets("Sheet1")
    exportlastRow = exportWs.Cells(exportWs.Rows.Count, "E").End(xlUp).Row
    ThisWorkbook.Activate
    ws.Range("C10") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF([export.XLSX]Sheet1!E2:E" & _
        exportlastRow & "=C15,[export.XLSX]Sheet1!A2:A" & exportlastRow & ",""""))")
End Sub

Resources