VBA Method 'Range' of object '_Global' failed error

Hello,


(Edited for Forum Rule #3 compliance -- sorry about that)


I'm a newbie to VBA programming, but am pretty experienced with Excel formulas. I'm attempting to invoke the VLOOKUP worksheet function in a macro I am writing (see last 7 lines of my code below), but each time I run I get the Method 'Range' of object '_Global' failed error.

("lookuptab" is a named range already defined in the workbook.)

Any help would be appreciated. Again, I'm new to VBA, so I'm sure it's some basic misunderstanding on my part.

Thanks in advance,
Jim

Sub reformat1()
'
' reformat1 Macro
'

'
    
Dim NumCols As Integer, NumRows As Integer

    Rows("11:11").Select
    Selection.Find(What:="Team Member Mean", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    NumCols = ActiveCell.Column

    Columns("a:a").Select
    Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    NumRows = ActiveCell.Row


Range(Cells(NumRows, 1), Cells(NumRows, NumCols)).Select

    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    With Selection
        .NumberFormat = "0.0"
            .Font.Bold = True
    End With


Range(Cells(11, NumCols), Cells(NumRows, NumCols)).Select

    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    With Selection
        .NumberFormat = "0.0"
            .Font.Bold = True
    End With

Cells(11, NumCols).Select
ActiveCell.Value = "Team Member Mean"
Cells(11, NumCols + 1).Select
ActiveCell.Value = "Overall Satisfaction w/CVS Caremark"



Range(Cells(11, NumCols + 1), Cells(NumRows, NumCols + 1)).Select

    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    With Selection
        .NumberFormat = "0.0"
        .Font.Bold = True
    End With




  
Range(Cells(11, 2), Cells(NumRows, NumCols + 1)).Select

    Selection.ColumnWidth = 11
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Dim cell As Range
Dim ltab As Range

Set ltab = Range("lookuptab")

For Each cell In Range(Cells(12, NumCols + 1), Cells(NumRows - 1, NumCols + 1))
cell = Worksheet.Function.VLookup((Range("cell").Offset(0, 0 - NumCols)), ltab, 109, False)
Next cell


End Sub

Welcome to this forum. Please read the forum rules.

It will explain that you should use code Tags for readability.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Thanks for pointing this out Ricardo

    Dim cell        As Range
    Dim ltab        As Range

    Set ltab = Range("lookuptab")

    For Each cell In Range(Cells(12, iCol + 1), Cells(iRow - 1, iCol + 1))
        cell = Worksheet.Function.VLookup((Range("cell").Offset(0, 0 - iCol)), ltab, 109, False)
    Next cell

1. Is "cell" a defined name on the worksheet? That's what the code shows.

2. Does .Offset(0, 0 - iCol) resolve to to a valid column?

3. Are there at least 109 columns in lookuptab?

Step through your code and look at variables as the code executes. Learning to debug is the biggest favor you could do for your VBA skills.

Hello JKCincy,

You have dimensioned the variable cell as a Range object. Later on you are trying to assign the VLookup value to it, which won't work. The code below has several problems.

For Each cell In Range(Cells(12, NumCols + 1), Cells(NumRows - 1, NumCols + 1))
cell = Worksheet.Function.VLookup((Range("cell").Offset(0, 0 - NumCols)), ltab, 109, False)
Next cell

Replace it with this code...

Dim Result As Variant

For Each cell In Range(Cells(12, NumCols + 1), Cells(NumRows - 1, NumCols + 1))
  Result = Worksheet.Function.VLookup(cell.Offset(0, 0 - NumCols), ltab, 109, False)
Next cell

Also, Worksheet.Function.VLookup...
Should be WorksheetFunction.VLookup...; i.e., no period between worksheet and function