Index / match with variables

Hi,

My first post to this forum, so hi to everyone.

I'm having difficulties with the following.

I wish to use VBA to enter an INDEX/MATCH formulas into my workbook. I want the formula in the cell as if data changes this can update.

I have SheetA and SheetB. SheetB is where I want to enter the formula which refer to SheetA.

So I have the following

Public StartRow As Integer
Public EndRow As Long
Public ARng As Range
Public GRng As Range
' Above Public as this piece of code is part of a bigger project

Sub test()

'So I select SheetA to set my Ranges
Sheets("Sheet A").Select

StartRow = 1
EndRow = Cells(1048576, 1).End(xlUp).Row
ACol = 1
Gcol = 7

' My aim here is to specified all rows (variable in length) in Columns A & G as a range

Set ARng = Range(Cells(1, ACol), Cells(EndRow, ACol))
Set GRng = Range(Cells(1, Gcol), Cells(EndRow, Gcol))

Sheets("SheetB").Select
Cells(2, 8).Select ' Where I want the formula

' Now I thought THE BELOW would work but get a RUN-TIME ERROR 1004 APPLICATION-DEFINDED OR OBJECT-DEFINDED ERROR
' Obviously I was wrong..

ActiveCell.FormulaR1C1 = "=INDEX(ARng,MATCH((cells(1,2),GRng,0)))"
End Sub

So basicialy I want in G2 a formula looking like =INDEX(TGT_1st!$A$1:$A$3806,MATCH(A2,TGT_1st!$G$1:$G$3806,0))

Can anyone help me plse

Mr Jack

Fantastic. Sorry about missing out the CODE tags. A slight syntax error which I can see if an easy one to make but was able to see & correct this. Plus I can now use this to assist with other formulas I needs.