Help Debugging Short VBA Formula

[nirvehex]

Hi All,

I keep getting an error with this code:





Selection.AutoFilter


ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=20, Criteria1:= _
Array("CS", "DS", "L", "MN", "RR", "ROC"), Operator:=xlFilterValues

With Worksheets("Services").AutoFilter.Range
Range("Y" & .Offset(2, 0).SpecialCells(xlCellTypeVisible)(2).Row).Select
End With

With ActiveCell.FormulaR1C1 = _
"=VLOOKUP(CONCATENATE([@Location],""-"",[@ID]),Pivot!C:C[3],4,FALSE)"
Range("Y" & .Offset(2, 0).SpecialCells(xlCellTypeVisible)(2).Row).Select
End With



My table headers are in row 2 of my worksheet. Table = Table2. Worksheet = "Services".

All I'm trying to do is filter column T to specific values and then perform a Vlookup on the first visible cell below the header row in column Y and copy it down to the end of the table in that column. The first part works fine, but after that it errors out.

Any ideas?

Thank you!

*Cross Posted here: https://www.mrexcel.com/board/threads/help-debugging-short-vba-formula.1119906/

[p45cal]

Are you trying to do this?:
Sub blah()
With Sheets("Services").ListObjects("Table2")
On Error Resume Next 'in case the filter has been removed from the table.
.AutoFilter.ShowAllData 'clear existing filters
On Error GoTo 0
.Range.AutoFilter Field:=20, Criteria1:=Array("CS", "DS", "L", "MN", "RR", "ROC"), Operator:=xlFilterValues
With .ListColumns(25).DataBodyRange.SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = "=VLOOKUP(CONCATENATE([@Location],""-"",[@ID]),Pivot!C:C[3],4,FALSE)"
.Select
End With
End With
End Sub
This only puts formulae in the visible cells; I don't know if you want formulae in the whole column, visible cells and invisible cells.

[nirvehex]

Thank you so much!