Error passing a range to the vlookup function

Hi,

I'm trying to pass a range to the worksheetfunction.vlookup method and I can't seem to get it to work. The code I have is below and I've inserted a comment where the error I get occurs.

Sub GetDataSheetNames()

    Dim i As Integer
    Dim WorkSheetName As String
    Dim WorkSheetCount As Long
    Dim LastColumn As Long
    Dim DateCount As Long
    Dim SecurityName As String
    'Dim Wks As Worksheet
    Dim LookupRange As Range
    
    Application.ScreenUpdating = False
    
   'Count the number of worksheets
    WorkSheetCount = Worksheets.Count
    
    
'-------------------------------------------------------------
    'Count the number of rows and columns for the loops
    
    LastColumn = WorkSheetCount - 4
    DateCount = Cells(Rows.Count, "A").End(xlUp).Row
    
    For j = 0 To DateCount - 1
        For i = 1 To LastColumn - 1
            
            'Get the name of the security and set as the appropriate worksheet name
            Range("A2").Select
            SecurityName = ActiveCell.Offset(0, i).Value
            
            Set LookupRange = Sheets(SecurityName).Range("A:I")
            
' The following line is error code to check that the correct range is being selected
' It doesn't and I get the error at this point
            Range(LookupRange).Select
            
            ActiveCell.Offset(j, i).Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(j, 0), Worksheets(SecurityName).Range("A:I"), 9, 0)
        
        Next i
    Next j
    
    Application.ScreenUpdating = False

End Sub

When I try to run this it stops at

Range(LookupRange).Select

with the error "Runtime Error 1004 - Method 'Range' of object '_Global' failed."

Any suggestions on how I might remedy this would be most appreciated!

Kindest Regards,
Tim