Apply Absolute Reference to multiplie cells

L

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,""))
 
G

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

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,""))
Click to expand...

Gord Dibben MS Excel MVP
 
L

lostinformulas

Gord Dibben,


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

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

lostinformulas

I was able to run the Macro it work in some of the cells however other
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 th
formula???
 
G

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

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?
Click to expand...

Gord Dibben MS Excel MVP
 
G

Gord Dibben

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


Gord
 
R

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.
 
Top