Fix expected end of statement in VBA

[nando88]

I am trying to use a filter function with data from a seconf workbook. I have the following formula, but I am getting expected end of statement in the first Range and I don't know why:

sub tenplate()
Dim gen As String, template As String, lr As Long, data As String, name As String, i As Long, wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
gen = ThisWorkbook.name
data = archivo
Set wb1 = Workbooks(gen)
Set wb2 = Workbooks(data)
Set ws1 = Sheets("Trabajo")
Set ws2 = Sheets("Rpt")
For i = 1 To Worksheets.Count
Workbooks(gen).Activate
Worksheets(i).Activate
name = ActiveSheet.name
wb1.ws1.Range("A6").Formula2 = "=filter(wb2.ws2.Range("A6:P65536"),wb2.ws2.Range("C6:C65536")=wb1.ws1.Range("D1"),"""")"
end sub

 

[rlv01]

I think because you are mixing objects into your excel formula.

Perhaps this (not tested)

wb1.ws1.Range("A6").Formula2 = "=filter(" & _
    wb2.ws2.Range("A6:P65536").Address & "," & _
    wb2.ws2.Range("C6:C65536").Address & "=" & wb1.ws1.Range("D1").Address & "," _
    & Chr(34) & Chr(34) & ")"

 

[Alex Blakenburg]

1) Since you are working with 2 workbooks, I would expect your 2 "Set ws = " lines to specify which workbook the sheet is in.

Set ws1 = Sheets("Trabajo")      ' Which workbook  eg ws1 = wb1.Sheets("Trabajo")
Set ws2 = Sheets("Rpt")            ' Which workbook

2) You can't us the syntax "wb2.ws2" the ws2 is already in a workbook so the wb2 is doubling up, so modifying @rlv01 correction gets you to this:

ws1.Range("A6").Formula2 = "=filter(" & _
    ws2.Range("A6:P65536").Address & "," & _
    ws2.Range("C6:C65536").Address & "=" & ws1.Range("D1").Address & "," _
    & Chr(34) & Chr(34) & ")"

3) I don't know if you only gave us an extract but
- archivo is not a valid workbook name
- there is no "Next i" at the end

 

[rlv01]

2) You can't us the syntax "wb2.ws2" the ws2 is already in a workbook so the wb2 is doubling up...

Good point.

 

[RoryA]

You'll also need to include the workbook and sheet names in the formula. Adding the External:=True parameter to Address should suffice.