Apply Absolute Reference to multiplie cells
 

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

Apply Absolute Reference to multiplie cells
 

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
<lostinformulas.29av8b_1150133705.7209@excelforu m-nospam.com 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

Apply Absolute Reference to multiplie cells
 

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
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551116

Apply Absolute Reference to multiplie cells
 

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????


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551116

Apply Absolute Reference to multiplie cells
 

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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macros by going to ToolMacroMacros.

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
<lostinformulas.29axjp_1150136703.9567@excelforu m-nospam.com 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

Apply Absolute Reference to multiplie cells
 

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
<lostinformulas.29azv0_1150139704.9181@excelforu m-nospam.com 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????

Apply Absolute Reference to multiplie cells
 

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