Forum Discussion

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

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

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 & ",""""))")

HansVogelaar

MVP

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

hrh_dash
Iron Contributor
May 22, 2022
the code does not seemed to have any difference as compared to the earlier code..
- HansVogelaar
  MVP
  May 22, 2022
  hrh_dash
  
  The variable exportlastRow was declared as a Range, now as a Long.
hrh_dash
Iron Contributor
May 22, 2022
HansVogelaar , it is still showing an error: "Run-time error '1004': Application-defined or object-defined error
- HansVogelaar
  MVP
  May 22, 2022
  hrh_dash
  
  Which line causes the error?
  - hrh_dash
    Iron Contributor
    May 22, 2022
    HansVogelaar , first line is throwing out the error