Vlookup with sheet names stored in array

[Syaza Azman]

Hi,
i'm trying to vlookup some comments from different workbooks but with the same name.
so i stored the names in an array.

i got 2 problems here,

1. first vlookup runs without an error but doesnt return any result
2. second vlookup gives error 1004; Application-defined or object deifned error

here's the code

For Each TabName In Array("report", "Pending", "Created")
        
        On Error GoTo NextTabName8
                      
            If TabName = "report" Then
            
                VLatCell = "T3"
                AutoFillIndicator = "R3"
                
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range("T1").Value = TabName
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-5],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C20,6,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "T3").Select
                Selection.FillDown
            
            Else
            
            
                VLatCell = "S2"
                AutoFillIndicator = "R2"
                
                        
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-4],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C19,5,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "S2").Select
                Selection.FillDown
        
            End If


NextTabName8:
Next TabName

 

[Syaza Azman]

Nevermind,
i've got it.



ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C19,5,0)"


is supposed to be




ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'[Work Order Report - 8 Week.xlsx]" & TabName & "'!C15:C19,5,0)"

Hi,
i'm trying to vlookup some comments from different workbooks but with the same name.
so i stored the names in an array.

i got 2 problems here,

1. first vlookup runs without an error but doesnt return any result
2. second vlookup gives error 1004; Application-defined or object deifned error

here's the code

For Each TabName In Array("report", "Pending", "Created")
        
        On Error GoTo NextTabName8
                      
            If TabName = "report" Then
            
                VLatCell = "T3"
                AutoFillIndicator = "R3"
                
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range("T1").Value = TabName
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-5],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C20,6,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "T3").Select
                Selection.FillDown
            
            Else
            
            
                VLatCell = "S2"
                AutoFillIndicator = "R2"
                
                        
                Windows("Work_Order_Report__Weekly_Repo.xlsx").Activate
                Sheets(TabName).Select
                Range(VLatCell).Select
                ActiveCell.FormulaR1C1 = _
                    "=VLOOKUP(RC[-4],'[Work Order Report - 8 Week.xlsx]" & "TabName" & "'!C15:C19,5,0)"
                    
                Range(AutoFillIndicator).Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 2).Select
                Range(Selection, "S2").Select
                Selection.FillDown
        
            End If


NextTabName8:
Next TabName