Trouble with Tables

**Danry** · 06-11-2012, 09:46 PM

Hi,

I want my VBA to do a vlookup in another spreadsheet to find the name of the region for the report. However, I keep getting the following error message: "Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class." The code is as follows:

Private Sub FindRegionName()

    
    Dim LastCell
    Dim Table
    
    
    Workbooks.Open Filename:="J:\Planning & Resources\Portfolio Office\Finance\Budget 2012\Management reports 2012\International\Regional Report Names.xls"

    Range("A1").Select
    
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    
    RCode = Range("a2").Value
        
    LastCell = ActiveCell.Address

    Table = ("A1:" & LastCell)
    
    RName = Application.WorksheetFunction.VLookup(RCode, Table, 2, False)

    ActiveWorkbook.Close

End Sub

I have tried to use the actual variable as a test and I still get the same error message so it's not that the variable isn't in the table.

Any suggestions?

Cheers,
Danry

**protonLeah** · 06-11-2012, 10:10 PM

Try this one:

 Option Explicit

Private Sub FindRegionName()
    Dim Table   As Range, _
        rcode   As Variant, _
        rname   As Variant
    
    Workbooks.Open Filename:="J:\Planning & Resources\Portfolio Office\Finance\Budget 2012\Management reports 2012\International\Regional Report Names.xls"
    
    rcode = Range("a2").Value
        
    Set Table = Range("A1").CurrentRegion
    
    rname = Application.WorksheetFunction.VLookup(rcode, Table, 2, False)
    ActiveWorkbook.Close
End Sub

**JapanDave** · 06-11-2012, 10:19 PM

Try this

Private Sub FindRegionName()
    
    Dim RCode As Range, Table As Range, RName As String
    
      Workbooks.Open Filename:="J:\Planning & Resources\Portfolio Office\Finance\Budget 2012\Management reports 2012\International\Regional Report Names.xls"

        Set RCode = Range("a2")

           Set Table = Cells(1, 1).Resize(Rows.Count, Columns.Count)
                RName = Application.WorksheetFunction.VLookup(RCode, Table, 2, False)
           MsgBox RName

    ActiveWorkbook.Close

End Sub

Edit: protonLeah beat me to it!