0

I try to copy a Formular to a cell. The Lookup-Value of the INDEX(MATCH)function musst be dynamik. I already wrote a working code that give me the required rownumber and I have a fix columnumber.

Cells(findrow, 6) = "=INDEX(C:C;MATCH(" & Cells(findrow, 16) & ";DD;0))"

Unfortunately It doesn´t paste the formular in the Cell I want.

This doesn´t work either

Cells(findrow, 6).Formular = "=INDEX(C:C;MATCH(" & Cells(findrow, 16) & ";DD;0))"

findrow contains the correct row.

Does anyone has an idea, what I did wrong?

Thanks in advance

BigBen
  • 46,229
  • 7
  • 24
  • 40
Djerun
  • 37
  • 5

1 Answers1

0

Perhaps:

Cells(findrow, 6).Formula = "=INDEX(C:C,MATCH(P" & findrow & ",D:D,0))

The issues:

  • Use .Formula, not .Formular (?).
  • When using .Formula, you need to use a comma , as the separator, not ;. See this for more detail.
  • You forgot to include the : in the D:D.
  • & Cells(findrow, 16) & is implictly attempting to concatenate the value of that cell when you just need its address. Since all you need is the address and you have a fixed column, you can hard-code the column letter and just concatenate findrow: "...MATCH(P" & findrow & ...
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • It inserts the formula now, but the lookup value needs to be cell R100 and it´s just 100 with your solution. I need to address the last cell in the row I stored in ```findrow``` – Djerun Aug 18 '20 at 13:42
  • You have a hard-coded `16` in your question - that is column `P`, which I've hard-coded in the formula: `"=INDEX(C:C,MATCH(P"`... Where are you getting `R` from? If you need `R`, just change the `P` to `R`. But it's most certainly not true that "it's just 100." – BigBen Aug 18 '20 at 13:43