Reference a Column Number in a Range

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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:
Code:
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:
Code:
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:
Code:
ColumnLetter = Split(Cells(1, LastColumn).Address, "$")(1)

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

Code:
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,590
Office Version
  1. 365
Platform
  1. Windows
You need to qualify the cells
Code:
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
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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:

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

or

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

versus

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

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

Code:
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:

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,590
Office Version
  1. 365
Platform
  1. Windows
This
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,932
Members
409,847
Latest member
Foster034
Top