Cell referencing in VBA

R

Rob

I am having a problem getting a cell address to be referenced relative in a
macro. If I use the activecell.address command the cell is absolute. If I
use the Row/Column command with [ ] to signify relative reference the
reference is relative to the location of the formula and not the original
cell. If I select cell D5 as part of my coding I want D5 to be used not
$D$5. What should I do?

Thanks,

Rob
 
R

Rob

Beth,

I found the answer. I have to add parameters to the ActiveCell.Address
command:

ActiveCell.Address(False, False, xlA1, False, False). This makes the cell
reference relative. Do you know how to extract the cell row letter from a
cell using ActiveCell.Address? When I use ActiveCell.Address.Column I get a
number instead of the letter.

Thanks for the help.

Rob


Beth Melton said:
Are you using the Macro Recorder? If so, click the Relative References
button.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email cannot be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Coauthor of Word 2007 Inside Out:
http://www.microsoft.com/MSPress/books/9801.aspx#AboutTheBook

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

Rob said:
I am having a problem getting a cell address to be referenced relative in a
macro. If I use the activecell.address command the cell is absolute. If
I
use the Row/Column command with [ ] to signify relative reference the
reference is relative to the location of the formula and not the original
cell. If I select cell D5 as part of my coding I want D5 to be used not
$D$5. What should I do?

Thanks,

Rob
Click to expand...
Click to expand...
 
B

Beth Melton

I'm not sure if you want to convert the R1C1 referencing to A1 style
referencing or simply be able to note which row/column is being referred to
visually in your workbook, but to convert, take a look at
Application.ConvertFormula.

To view R1C1 referencing style in your workbook, in Excel Options, turn on
the "R1C1 Reference style" option.

Also note there is a dedicated newsgroup for Excel VBA. You may want to post
these types of questions there to obtain a broader Excel VBA audience. :)

Please post all follow-up questions to the newsgroup. Requests for
assistance by email cannot be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Coauthor of Word 2007 Inside Out:
http://www.microsoft.com/MSPress/books/9801.aspx#AboutTheBook

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

Rob said:
Beth,

I found the answer. I have to add parameters to the ActiveCell.Address
command:

ActiveCell.Address(False, False, xlA1, False, False). This makes the cell
reference relative. Do you know how to extract the cell row letter from a
cell using ActiveCell.Address? When I use ActiveCell.Address.Column I get
a
number instead of the letter.

Thanks for the help.

Rob


Beth Melton said:
Are you using the Macro Recorder? If so, click the Relative References
button.
Rob said:
I am having a problem getting a cell address to be referenced relative
in a
macro. If I use the activecell.address command the cell is absolute.
If
I
use the Row/Column command with [ ] to signify relative reference the
reference is relative to the location of the formula and not the
original
cell. If I select cell D5 as part of my coding I want D5 to be used
not
$D$5. What should I do?

Thanks,

Rob
Click to expand...
Click to expand...
Click to expand...
 
Top