Apply Absolute Reference to multiplie cells

[lostinformulas]

I have a long formula that I have applied in multiple cell once it is in the cell I need this formula to be absolute.

Is there an easy way or do i have to go into each cell and add the $??

=IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="express"),1,""))

[Gord Dibben]

Try these. Ignores cells without formulas.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 12 Jun 2006 12:31:06 -0500, lostinformulas
<[email protected]> wrote:

>
>I have a long formula that I have applied in multiple cell once it is in
>the cell I need this formula to be absolute.
>
>Is there an easy way or do i have to go into each cell and add the $??
>
>=IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="express"),1,""))

Gord Dibben MS Excel MVP

[lostinformulas]

Gord Dibben,


I haven't really ever use VB. Can you give me more details how to apply the code that you supplied.

I know to go into VB Editor and sellect the worksheet that I want to Apply this to then I pasted the code that you supplied. What else Do I need to do? And how will I be able to see if it worked? Will the formula actually show up with the absolute refence or will it be behind the scene?

[lostinformulas]

I was able to run the Macro it work in some of the cells however others received and error message of #VALUE.

The difference between the formulas that work and the one that didn't

The code worked in the cells that had the following formula:

=IF(AND('1'!$AP64="yes",'1'!$A64="yes"),1,"")

The cells that didn't work had the following formula:

=IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64="HB",'1'!$D64="yes"),0.5,IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64="HB"),1,""))

Could it have something to do with the second If statement in the formula????

[Gord Dibben]

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

There are 4 macros in my original posting. Each will change references in a
particular manner.

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macros by going to Tool>Macro>Macros.

You can also assign these macros to a button or a shortcut key combo


Gord Dibben MS Excel MVP

On Mon, 12 Jun 2006 13:22:26 -0500, lostinformulas
<[email protected]> wrote:

>
>Gord Dibben,
>
>
>I haven't really ever use VB. Can you give me more details how to apply
>the code that you supplied.
>
>I know to go into VB Editor and sellect the worksheet that I want to
>Apply this to then I pasted the code that you supplied. What else Do I
>need to do? And how will I be able to see if it worked? Will the
>formula actually show up with the absolute refence or will it be behind
>the scene?

Gord Dibben MS Excel MVP

[Gord Dibben]

Changing cell references from relative to absolute should not give you the
#VALUE error.


Gord

On Mon, 12 Jun 2006 14:14:59 -0500, lostinformulas
<[email protected]> wrote:

>
>I was able to run the Macro it work in some of the cells however others
>received and error message of #VALUE.
>
>The difference between the formulas that work and the one that didn't
>
>The code worked in the cells that had the following formula:
>
>=IF(AND('1'!$AP64="yes",'1'!$A64="yes"),1,"")
>
>The cells that didn't work had the following formula:
>
>=IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64="HB",'1'!$D64="yes"),0.5,IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64="HB"),1,""))
>
>Could it have something to do with the second If statement in the
>formula????

[Rothman]

Microsoft should make the Relative and Absolute subroutines standard. I have
to generate sheets using a mix of absolute and relative references all the
time, only to have to turn around and wish that they were all relative so I
can have their functions apply to other data.

This is a heck of a useful thread.

"lostinformulas" wrote:

>
> I have a long formula that I have applied in multiple cell once it is in
> the cell I need this formula to be absolute.
>
> Is there an easy way or do i have to go into each cell and add the $??
>
> =IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="express"),1,""))
>
>
> --
> lostinformulas
> ------------------------------------------------------------------------
> lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
> View this thread: http://www.excelforum.com/showthread...hreadid=551116
>
>

[spaul]

Thank you for the macros. Very helpful.

Try these. Ignores cells without formulas.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 12 Jun 2006 12:31:06 -0500, lostinformulas
<[email protected]> wrote:

>
>I have a long formula that I have applied in multiple cell once it is in
>the cell I need this formula to be absolute.
>
>Is there an easy way or do i have to go into each cell and add the $??
>
>=IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="Express",'1'!$D70="yes"),0.5,IF(AND('1'!$A70="yes",'1'!$AP70="yes",'1'!$AT70="express"),1,""))

Gord Dibben MS Excel MVP