Selecting columns by index number

Question

I want to detect and highlight changes in a given Table A by comparing it with some other Table B.

I get

error number 1004: range_global failed.

Here is a part of my attempt:

Option Explicit

Sub changeFinder()
    ...
    For j = 1 To m
        sht.Columns(j + 1).Insert
        sht.Columns(j + 1).Insert
        For i = 2 To n
            sht.Cells(i, j + 1).Value = Application.VLookup(sht.Cells(i, j), sht2.Range("Columns(1):Columns(j)"), j, False)
        Next i
        ...    
    Next j

End Sub

My goal is for Table A: To loop over every column j. And for every column j I want to create two temporary columns j+1 and j+2 to the right.
I want to fill j+1 with VLookup over the columns 1 to j from Table B.
The latter seems to fail.

Is there a "natural" way to select the Columns 1 to j+1 from a specific sheet, when I don't know the alphabetical name of these columns, but only the index numbers?

Answer 1

Using Application.VLookup

• Replace

  sht.Cells(i, j + 1).Value = Application.VLookup(sht.Cells(i, j), sht2.Range("Columns(1):Columns(j)"), j, False)`
  

  with:

  sht.Cells(i, j + 1).Value = Application.VLookup( _
      sht.Cells(i, j), sht2.Columns(1).Resize(, j), j, False)
  

• But I think you would be better off by using Application.Match.

• Also, note that a backward loop will be necessary for this to work correctly:

  For j = m To 1 Step -1
  

  You don't want to lookup in the inserted columns.