Convert an address into a Relative Address?

[rkcim]

I have an Absolute address, and I am trying to find the easiest way to convert it to a Relative address.

Any ideas?

 

[bydesign]

Rkcim

An excel address is absolute when a "$" is in front of the column or row

an absolute column but relative row is "$A1"

a relative column but absolute row is "A$1"

an absolute row and column is "$A$1"

if you just have "A1" both row and column are relative...

Hope that helps!

 

[rkcim]

I know that but I was looking for something that will strip the $'s from the address. Is there a predifined function that can do this?
Example
MyAddress = $A$10
MyAddress = Relative(MyAddress)

Now MyAddress = A10

 

[bydesign]

Rkcim

Ahhhh, That's a different story!!! but not hard

1) Select the range of cells you want to find the "$" in the formula

2) Select Edit/Replace or (cntl h)

3) Click the options button at the bottom of the find and replace window... and make sure "look in formulas" is selected

4) in the "find what" box... enter "$"

5) in the "Replace with" box... enter " " (nothing)

6) click replace all

 

[RichardS]

You could try hitting F2 to edit the formula, then hit F4 to cycle through the various relative, absolute and combination formulae. Highlight the parts of the formula you want to change.

Richard

 

[Yogi Anand]

I know that but I was looking for something that will strip the $'s from the address. Is there a predifined function that can do this?
Example
MyAddress = $A$10
MyAddress = Relative(MyAddress)

Now MyAddress = A10

Hi rkcim:

Here is a formula based approach ...

Book2
	C	D	E	F
3
4		MyAddress
5		$A$10	A10
6
Sheet6

Would this help?

 

[rkcim]

Mybe but can it be used in VBA?

I found a solution but its kind of lengthy:
RowRange = chkBox.TopLeftCell.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)

I am thinking this will return a relative address. Here is what the help has to say:
Range object (Syntax 2): Returns the range reference in the language of the macro. Read-only String.

Syntax 1

expression.Address

Syntax 2

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

expression Required. An expression that returns a Hyperlink object (Syntax 1) or a Range object (Syntax 2).

RowAbsolute Optional Variant. True to return the row part of the reference as an absolute reference. The default value is True.

ColumnAbsolute Optional Variant. True to return the column part of the reference as an absolute reference. The default value is True.

ReferenceStyle Optional Variant. Can be one of the following XlReferenceStyle constants: xlA1 or xlR1C1. Use xlA1 to return an A1-style reference. Use xlR1C1 to return an R1C1-style reference. The default value is xlA1

External Optional Variant. True to return an external reference. False to return a local reference. The default value is False.

RelativeTo Optional Variant. If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point.

Remarks

If the reference contains more than one cell, RowAbsolute and ColumnAbsolute apply to all rows and columns.

 

[Yogi Anand]

Hi rkcim:

If you know the Row Number and the Column Number, then you can use the ADDRESS function in EXCEL as in ...

Book2
	D	E	F	G
2
3	Row	2	Column	4
4	$D$2	D$2	$D2	D2
5
Sheet6a

 

[rkcim]

Is there a similar function in Visual Basic?

 

[Yogi Anand]

Hi rkcim:

You have already refered to the Address Property in VBA in one of your earlier posts, So, you could use it as ...

RelAddress=Range("$A$10").Address(Rowabsolute:=false,columnabsolute:=false)

to get A10 as the result.