Referencing a Specific Cell in a moving set of Data

[PontusCuan]

I am trying to write a bit of code that inserts a formula into one cell and then loops to insert the same formula in the cell beneath it and so on until I want it to stop. It looks like this at the moment:

---

Counter = 1


Do While Counter < 22


myRange.Offset(Counter, 3).FormulaR1C1 = _
"=INDEX('Technical Info - Platforms'!R4C2:R24C12,MATCH(RC[-2],'Technical Info - Platforms'!R4C3:R24C3,0),MATCH(R[-1]C[0],'Technical Info - Platforms'!R3C2:R3C12,0))"
Counter = Counter + 1


Loop

---

The Problem I believe lies in the Bold and Underlined area of code.

This is a small part of a larger project - I want the whole macro to insert a table in a specific destination.
The problem is that In the second MATCH function I want to reference the cell at the top of the column every time but if I use an absolute reference the table will not be able to move without breaking the function. However this function does not work wither as it is effectively always referencing the cell above to cell I want the formula to go in. I want to know if there is a way of referencing the top cell every time.

If this isn't possible i had another idea of somehow putting an equation to determine the cell address e.g.

---

Counter = 1


Do While Counter < 22


myRange.Offset(Counter, 3).FormulaR1C1 = _
"=INDEX('Technical Info - Platforms'!R4C2:R24C12,MATCH(RC[-2],'Technical Info - Platforms'!R4C3:R24C3,0),
MATCH(R[0 - Counter]C[0],'Technical Info - Platforms'!R3C2:R3C12,0))"
Counter = Counter + 1


Loop

---

The idea here is that the R value changes every loop depending on the Counter Value so the top cell will always be referenced. Sadly this hasn't worked for me - I get an error stating that the syntax is wrong or the formula returns the REF# error.

Is it possible to reference a cell this way at all and, if so, how would I do it?

Any help is appreciated,

Thank you

 

[Rijnsent]

Hi PontusCuan,

' You are basically gluing bits of text together, text is indicated by "", so you can glue them together like in an excel function: "a" & "b" & "c" & "d"

myRange.Offset(Counter, 3).FormulaR1C1 = _
"=INDEX('Technical Info - Platforms'!R4C2:R24C12,MATCH(RC[-2],'Technical Info - Platforms'!R4C3:R24C3,0),
MATCH(R[" & 0 - Counter & "]C[0],'Technical Info - Platforms'!R3C2:R3C12,0))"

Cheers,
Koen