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 expo...
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
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
      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        May 19, 2022

        HansVogelaar , vba is showing an error - "Run-time error '91' Object variable or with block variable not set

         

        Should i have 2 exportlastRow1 and exportlastRow2 as i have 2 different columns; A and E?

        and 

        Should the C15 be right after IF function?

        Dim exportlastRow1 As Range
Dim exportlastRow2 As Range

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

ws.Range("C10") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF(C15 = [export.XLSX]Sheet1!E2:E" & _
exportlastRow1 & ",[export.XLSX]Sheet1!A2:A" & exportlastRow2 & ",""""))")

         

Resources