Solved: Set variable to a cell address

[goodwin57]

How would you go about setting a variable to be a cell address? At the moment I'm trying to use:


Dim MyCell as Range
Set MyCell = Range(Target.Address(True,True,x1R1C1,False))
RowIndex=MyCell.Row


This gives me a run time error 1004. Method 'Range' of object'_Worksheet' failed. Any suggestions on how to fix this?
Also, the last line should store RowIndex as the row number the target cell is on - but seems to not work (I've made a cell based on its value and the value isn't correct).

Thank you for any help,

Simon

[mikerickson]

The constant is xLR1c1 not x ONE R1C1

Also if your Excel-Option-General is set to A1 reference style, an R1C1 cell address will cause an error when used as the argument of Range.

[Norie]

Simon

Why not just this?


Set MyCell = Target

[Simon Lloyd]

Also if MyCell is always going to be Target you could just use:
Target.Row instead of bloating your code with variables

[goodwin57]

Mikerickson - thanks for the response, stupid error on my part there. I tried using the correct value, and the alternative but neither worked.
Norie - I was originally using Set MyCell = Target, but I wasn't sure if it was keeping the address properly throughout my sub.

I'm concerned because the MyCell.Row function seems to be holding the wrong value row value. I guess I'll need to look elsewhere for errors in the code! Thank you for your help to both of you
Cheers

Simon

[Simon Lloyd]

Dim MyCell As String
MyCell= Target.Address
Target.Row

[goodwin57]

Hi Simon.
Thanks for your suggestions, I've used both of them and my original problem has now gone. I'll mark this solved and post further problems elsewhere :). Cheers,

Simon