Fill Formula up to last empty adjacent Column

Is it the same formula that you need to copy, changing the column from 3 to 4, then to 5, etc?

There are many ways to go about it.

One of them could be - you could loop through the columns. You could also use the FormulaR1C1 method of inputting the formula and use it along with the loop to put the formula in.

Originally Posted by Floydlevedale

Dont understand what you mean by the above.

You have used formula so you can directly put in in the vlookup formula like this

.Range(.Cells(2, i), .Cells(10, i)).Formula = "=VLOOKUP(C2,$B$2:$I$1000," & i & ",0)"

If you use FormulaR1C1 reference, the method of writing will be different. Instead of saying range A2, in formulaR1C1, you would be saying R[2]C if you are in the same column. Check it up online too and you will understand.

Try this

Sub Fill_Across()
'ENTERS AND FILLS A FORMULA ACROSS
    Dim lngLastColumn As Long, i As Long

With Worksheets(1)
    lngLastColumn = .Range("IV1").End(xlToLeft).Column
    
    For i = 52 To lngLastColumn
        .Range(.Cells(2, i), .Cells(10, i)).Formula = "=VLOOKUP(C2,$B$2:$I$1000," & i & ",0)"
    Next i
End With

End Sub

It will check for the last column based on what is populated in the 1st row.

Then it loops populating each column. Note the formula also, has the i there to allow it to loop through the columns.

If you take sheet1, for which columns do you want the formula to be autofilled? Also will the formula always be in row 4?

So will the formula be in the first column and it needs to be auto-filled uptil the last column?

Try this code

Sub autofill_formulae()
Dim i As Long, lcol As Long

For i = 1 To Worksheets.Count
    With Worksheets(i)
        lcol = .Range("IV1").End(xlToLeft).Column
        .Range("A4").Copy .Range(.Cells(4, 2), .Cells(4, lcol))
        Application.CutCopyMode = False
    End With
Next i

End Sub