Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: variables and strings

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    Hi Mike

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

    F

  4. #4
    Join Date
    Apr 2004
    Posts
    11
    ActiveCell.FormulaR1C1 = "=indirect(address(" & myRow + 1 & ",2))"

    ... does the job!

    F

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •