Application defined or object defined error 1004 and Error 438 in using vlookup formula

Question

I am trying to use the vlookup function on a range of cells, that looks up values in other sheets. However I ckeep getting a runtime error that says "application defined or object defined error."

ActiveSheet.Range("$A$1", Selection.End(xlDown)).RemoveDuplicates Columns:=1, _
    Header:=xlYes
'In Summary Tab
Range("A1").CurrentRegion.Select
nRows = Selection.Rows.Count

' Places column headers in "Summary" tab
For iCounter = 2 To Sheets.Count
    Sheets(iCounter).Select
    Range("A1").CurrentRegion.Select
    nCols = Selection.Columns.Count
         For iColumn = 2 To nCols
            Sheets(iCounter).Select
                If (WorksheetFunction.IsNumber(Cells(2, iColumn)) = "TRUE") Then
                Cells(1, iColumn).Select
                Selection.Copy
                Sheets("Summary").Select
                ActiveCell.Offset(0, 1).PasteSpecial
                Application.CutCopyMode = False
                ActiveCell.Offset(1, 0).Select
                ActiveCell.Resize(nRows - 1, 1).Select
                Selection.Formula = "=vlookup(B2," & _
                    Range(sheets(icounter).selection).Address","& icolumn",false)"
                End If
        Next
Next

I also tried editing the vlookup formula to this, (with everything else the same):

                Selection = Application.WorksheetFunction.VLookup( _
                    "B2", Sheets(iCounter).CurrentRegion.Select, iColumn, False)

But this gives error 438 "object doesn't support this property or method"

Even tried editing the vlookup to this but got the 1004 error again:

Selection = Application.WorksheetFunction.VLookup("B2",Sheets(iCounter).Range(Cells(1, 1), Cells(nCols, nRows)), iColumn, False)

Answer 1

Try adding:

On error resume next

At the top of your script. If vlookup doesn't find a match, it will return an error.

If it returns an error, your script stops.

Your current & last proposals are essentially the same with different syntax - but I don't think that's the cause of your problem. Try on error next instead.

Answer 2

It appears as if you're attempting to insert formulas into certain ranges. Application.WorksheetFunction.VLookup doesn't insert a formula, it's used to return a value to your sub/function just like a normal function would do. So your top example is the correct approach if the goal is to insert a bunch of formulas.

The reason you're seeing an error on that line of code is because you aren't actually referencing a range. Try this instead:

Selection.Formula = "=vlookup(B2," & Sheets(icounter).Name & "!" & _
                        Selection.Address & "," & icolumn & ",false)"

Notice that this code calls out the name of the sheet, adds the exclamation point (!) and then adds the address of the selection.

You may also have been seeing errors because you were missing a few ampersands (&) in your code.

Finally, please beware of using .Select, .Selection, and .Activate. They have their place in VBA, but they will create many headaches for you. For more information on how to avoid using .Select, check out this answer by Chris Neilsen and this answer by Siddharth Rout.