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

But somehow I always get an error with 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 alphabetically name of these columns, but only the index numbers?

Thanks in Advance Best greetings, Julian

CodePudding user response：

I recommend you use Range.EntireColumn, as it's easier to introduce index numbers to the .Range property.
So the line within the second For loop becomes:

sht.Cells(i, j   1).Value = Application.VLookup(sht.Cells(i, j), sht2.Range("A1:A" & j).EntireColumn, j, False)

CodePudding user response：

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.