Excel Vlookup in macro with range

I'm trying to add a vlookup to a cell in an excel macro, however I can't seem to get the arguments of the function right nor the arguments of the range function

Please help me in stating how to write last line of my code:

            RangeStartR开发者_StackOverflowow = 2
            RangeStartColumn = 1 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            RangeEndRow = LastCol
            RangeEndColumn = 2 + 11 + (3 * (AverageSheetIndex - RowCounter - 1))
            ActiveCell.Formula = WorksheetFunction.VLookup(ActiveWorkSheet.Cells(1, c), ActiveSheet.range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)), 2, False)

I believe the code is straight (never mind the values of the first 4 lines), i wish to do a vlookup on the active cell to look for cell(1,c) in the range of the 4 values declared above.

Please let me know how to rewrite the last line of my code.

Thanks in advance.

ActiveCell.Formula requires a string representing the formula you want to have in the cell.

 ActiveCell.Formula = "=vlookup(" & activecell.offset(0,1).address & ....

I generally start by creating the formula manually in the sheet, then in the Debug window I type (for a formula in C3)

? range("C3").formulaR1C1

Then I edit the formula if required and I copy that in my code. Using formulaR1C1 avoid retrieving the letter: for an ActiveCell beeing $C$4,

"=D4" is equivalent to "=RC[1]"
"=$D$4" is equivalent to "=R4C4"

I f you really want to retrieve the column letter of MyCol, you can use:

split(cells(1,MyCol).address,"$")(1)

To help you rework your formula, I sometimes use the following sub, to be called from debug window:

Sub RngToVba(src As Range)
'Helps to write VBA code that will reproduce formula from existing sheet.
'writes (to the debug window) the VBA code to re-create the formulae in given range
'by Patrick Honorez - www.idevlop.com
'usage: from debug window, type RngToVba [L14:R14]
'                            or RngToVba range("L14:R14")
'then copy generated code to your module
    Dim c As Range
    For Each c In src
        Debug.Print "range(""" & c.Address & """).formula = """ & _
                    Replace(c.Formula, """", """""") & """"""
    Next c
End Sub