Reference a Column Number in a Range

[OilEconomist]

Thanks to all who attempt to assist. Will give feedback on solutions.

Why I am I getting a "Run-time error '1004'. Application-defined or object-defined error on the following line:

Sheets("Template.Line.Item.Data").Range(Cells(2, 13) & Cells(LastRow, i)).Formula = _
            "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"

For other reasons, I try to do the code like as follows, and get the same type or error:

Sheets("Template.Line.Item.Data").Range(Cells(2, 13) & Cells(LastRow, LastColumn)).Formula = _
            "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"

I am trying to use the least possible lines of code so I would like to avoid using something like this:

ColumnLetter = Split(Cells(1, LastColumn).Address, "$")(1)

I think I just need to modify this properly, but can't find anything on the net.

Range(Cells(2, 13) & Cells(LastRow, LastColumn))

I did dim i, j, LastRow, and LastColumn all as long.

Once again thanks and any suggestions will be much appreciated.

 

[Fluff]

You need to qualify the cells

With Sheets("Template.Line.Item.Data")
   .Range(.Cells(2, 13), .Cells(LastRow, i)).Formula = _
            "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"
End With

 

[mumps]

Try:

Range(Cells(2, 13), Cells(LastRow, i))

 

[OilEconomist]

Thanks for your help Fluff and mumps!

I now get a "Run-time error '13': Type mismatch"

I tried both suggestions where I included the "." before cells where I used:

(Cells(2, 13), Cells(LastRow, i))

or

(.Cells(2, 13), .Cells(LastRow, i))

versus

(Cells(2, 13) & Cells(LastRow, i))

Also, versus running a loop, I tried it for one column with:

With Sheets("Template.Line.Item.Data").Range(Cells(2, 13), Cells(LastRow, 13)).Formula = _
   "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"
            
End With

The following is the entire code:

Sub Incremental()
    Dim i As Long
    Dim j As Long
    
    Dim LastRow As Long
    Dim LastColumn As Long
    
    'Turn off Screen Mirroring/Updating
        Application.ScreenUpdating = False
    
    'Activating the worksheet "Template.Line.Item.Data"
        Worksheets("Template.Line.Item.Data").Activate


    'Find LastRow
        LastRow = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row


        
    'Finding last column
        LastColumn = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
         
        For i = 1 To LastColumn
            
            With Sheets("Template.Line.Item.Data").Range(Cells(2, 13), Cells(LastRow, i)).Formula = _
            "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"
            
            End With
               
            Sheets("Template.Line.Item.Data").Calculate
        
        Next i
        
        
        'ActiveSheet.Range.(Cells(2, 13), Cells(LastRow, i)).Copy
         
        'ActiveSheet.Range(Cells(2, 13), Cells(LastRow, i)).PasteSpecial Paste:=xlPasteValues
        
        Application.CutCopyMode = False
            
        ActiveSheet.Cells(2, 6).Select
        
         
End Sub

 

[Fluff]

This

With Sheets("Template.Line.Item.Data").Range(Cells(2, 13), Cells(LastRow, 13)).Formula = _
   "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"
            
End With

is not the same as i showed in post#2, it should be

With Sheets("Template.Line.Item.Data")
  .Range(.Cells(2, 13), .Cells(LastRow, 13)).Formula = _
   "=IFERROR(VLOOKUP( $A2 &"" ""& M$1, Data.Formatted!$E:$U, 3, FALSE), 0)"
            
End With