Cell referencing in VBA

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

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/boo...x#AboutTheBook

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

"Rob" <Rob@discussions.microsoft.com> wrote in message
news:9B9FEAD8-5C33-4617-88F2-3DEE4025B844@microsoft.com...
>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

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" wrote:

> 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/boo...x#AboutTheBook
>
> Word FAQ: http://mvps.org/word
> TechTrax eZine: http://mousetrax.com/techtrax/
> MVP FAQ site: http://mvps.org/
>
> "Rob" <Rob@discussions.microsoft.com> wrote in message
> news:9B9FEAD8-5C33-4617-88F2-3DEE4025B844@microsoft.com...
> >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
>
>
>

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/boo...x#AboutTheBook

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

"Rob" <Rob@discussions.microsoft.com> wrote in message
news:089237C8-A516-4798-ADE2-404DBA691BAF@microsoft.com...
> 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" wrote:
>
>> Are you using the Macro Recorder? If so, click the Relative References
>> button.

>> "Rob" <Rob@discussions.microsoft.com> wrote in message
>> news:9B9FEAD8-5C33-4617-88F2-3DEE4025B844@microsoft.com...
>> >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
>>
>>
>>