What I'm trying to do is to get a cell's value and use it in an expression. The problem is that the row number of that cell has been obtained by a match-search and is contained in the variable "myRow".

The following expression doesn't work

ActiveCell.FormulaR1C1 = "=indirect(myRow,2))"

because the variable "myRow" is contained in a string, therefore the string "myRow" is written instead of its value.

Anyone an idea how I can bypass this problem?

Thanks!

Fabalicious

Hi again

Try

ActiveCell.FormulaR1C1 = "=indirect(" & myRow & ",2))"

I am not familiar with indirect but I am not sure that is the correct for looking at the help for INDIRECT, but as before I am probably wrong.

Perhaps this instead

ActiveCell = "=indirect(" & cells(myRow,2).address & ")"

Let me know

MTB

Hi Mike

Both didn't work unfortunately.
I'll let you know when i got a solution, thanks anyway

F

ActiveCell.FormulaR1C1 = "=indirect(address(" & myRow + 1 & ",2))"

... does the job!

F

Thanks for letting me know

Address() is another worksheet function I don't recall using before, you live and learn.

Hence my pre-occupation with this, which also appeares to work

ActiveCell = "=indirect(" & Cells(myrow + 1, 2).Address & ")"


Thank again


MTB