Error passing a range to the vlookup function

**twills** · 08-21-2011, 07:38 AM

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

**snb** · 08-21-2011, 08:18 AM

There's no need to select anything here (nor almost ever in VBA). You get drowned in to many redundant variables..

For j = 0 To DateCount - 1
  For i = 1 To LastColumn - 1
    Range("A2").Offset(j, i).Value = Application.VLookup(Range("A2").Offset(j, 0), Sheets(range("A2").offset(,i)).Range("A:I"), 9, 0)
     Next
  Next
End Sub

**twills** · 08-21-2011, 02:17 PM

snb,

Many thanks for the speedy response - the code is much more concise without all of the select lines.

When I run the code, I get a Run Time Error 13, Type Mismatch. Hovering over the selected code where the runtime error occurs, it seems like the vlookup is looking up the correct Arg1 value and it's looking at the correct sheet for Arg2. The Arguments 3 and 4 are correct. Could my syntax of the range for arg2 be incorrect?

Kindest Regards,
Tim

**snb** · 08-21-2011, 02:42 PM

use F8 in the VBEditor to debug.

use the local variables window to see the variables' values.

this will suffice:

Sub snb()
  For j = 1 To sheets.count
    For i = 1 To columns(1).specialcells(2).count
      cells(j+1,i+1) = Application.VLookup(cells(j+1,i+1).value, Sheets(cells(j+1,i+2).value).Range("A:I"), 9, 0)
    Next
  Next
End Sub