Copy Vlookup Formula to alternate columns

I have a very large spreadsheet with over 400 columns of data, I wish to copy a Vlookup formula to each alternate column, this is so I can validate data between two different spreadsheets.
The number of rows in the spreadsheets differ, but I also need to be able to copy and paste the formula into each row of each alternate column as well. I have been looking at various methods to do this but cannot find one that incorporates all the functions necessary to complete these tasks.
I am looking for a macro which could be edited to achieve this goal fr each spreadsheet. or one that can identify the total number of rows present, this could look in Column A, and for the last cell in row 1, which contains my column header data + 1 being the last column I would need to paste the formula.
Also, when pasting the formula to the alternate columns, I need to amend the Col_Index_Num in the Vlookup formula to increment by one for each entry, so it refers to the correct column in the lookup spreadsheet.

The one saving grace is that there are only between 90 and 500 rows of data in the final spreadsheets, the width is the main problem, I do not fancy having to insert all these by hand.
Any help would be appreciated.

If you showed us the formula you would paste into A500 and then the formula you would paste into C500, that may be enough for use to recommend ONE formula you can place in A500 and just COPY across the whole row and it still do what you want.

What are those two formulas?

So, the first formula you wanted in cell ______ was __________________________?

The second formula you wanted in cell ______ was __________________________?


We can work out how far down to copy things later, just working on creating a single formula for you. Is there anything in the columns in between? No chance we can see a smaller sample showing example data and example formulas?

I see a VLOOKUP in C2 and E2... is that what you meant?

You can use the COLUMN() function and do some fun math until you get the result to equal the same as some incrementing number you want. For instance...

=COLUMN() placed in cell C2 would result in 3... because column C is column 3. So, how do we create a formula with COLUMN() that will convert 3 to 2 when placed in column C, 5 to 3 when placed in column E, 7 to 4 when placed in column G, etc.

=(COLUMN()-1)/2+1 ....does it.

So in your original C2 Vlookup formula, we replaced the hardcoded 2 in the third parameter with that formula, and now your VLOOKUP will adjust itself as you copy from column to column.

=VLOOKUP($A2,'N:\Marches\[External survey data-Rev 010711-Sample.xlsm]Sheet1'!$A$2:$GZ$35,(COLUMN()-1)/2+1,FALSE)

Which column is used to determine how far down to copy?

Hmm, your original question made it seem like the blank columns would be every other column... that you would want the formula placed in column B, then D, then F, etc... but now you say "in blank columns" and you have blank columns all over the place.

Can you provide more exact instructions as to where the formulas go?

So, this should do it:

Option Explicit

Sub InsertFormulas()
Dim RNG As Range, Cell As Range
Dim Rws  As Long

Set RNG = Rows(1).SpecialCells(xlBlanks).Offset(1)
Rws = Range("A" & Rows.Count).End(xlUp).Row - 1

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Cell In RNG
    Cell.Resize(Rws).FormulaR1C1 = _
        "=VLOOKUP(RC1,'N:\Marches\[External survey data-Rev 010711-Sample.xlsm]Sheet1'!R2C1:R35C208,(COLUMN()-1)/2+1,FALSE)"
Next Cell

Application.ScreenUpdating = True
End Sub

The code works and does the job required . Many thanks for your help.