insert single absolute symbol in formula

[sarahds]

Hi,
I am rolling over the spreadsheets for the new year and wonder if
there's an easy way to insert a single absolute into the formula.
i.e. if I highlight the formula and press F4 it puts absolutes in for
both the column and the row reference, whereas I only want to change
the column to absolute. Any ideas?

 

[Dave Peterson]

Keep on pressing the F4 key. It'll cycle through all 4 possibilities--absolute
row and column through relative row and column.

 

[Joerg Mochikun]

Press F4 3 times instead of once.

 

[sarahds]

Hi,
I am rolling over the spreadsheets for the new year and wonder if
there's an easy way to insert a single absolute into the formula.
i.e. if I highlight the formula and press F4 it puts absolutes in for
both the column and the row reference, whereas I only want to change
the column to absolute.  Any ideas?

Cool - many thanks. Now, how can I do that on a large block of cells
in one stroke?

 

[Gord Dibben]

Select the block of cells and run the AbsoluteCol macro.

I threw the others in just in case you need any other combination.

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

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)
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)
Next
End Sub


Gord Dibben MS Excel MVP

 

[sarahds]

Select the block of cells and run the AbsoluteCol macro.

I threw the others in just in case you need any other combination.

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

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)
        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)
        Next
    End Sub

Gord Dibben  MS Excel MVP




- Show quoted text -

I tried that, but I got an error with (Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
I changed the reference to be Cell.Formula, XlE3, xlE3, ...... as E3
was the starting row and column

 

[Joerg Mochikun]

First try the easy solutions, e.g Find& Replace.
Select your block of cells, and if the cells contain formulas like =A1, then
fire up Find&Replace (Ctrl+H) and replace '=" with '=$'. Adapt thisapproach
to your actual formula.

Hi,
I am rolling over the spreadsheets for the new year and wonder if
there's an easy way to insert a single absolute into the formula.
i.e. if I highlight the formula and press F4 it puts absolutes in for
both the column and the row reference, whereas I only want to change
the column to absolute. Any ideas?

Cool - many thanks. Now, how can I do that on a large block of cells
in one stroke?

 

[Dave Peterson]

Don't change those values.

But Gord had a couple of typos:

Option Explicit
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 Cell
End Sub
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 Cell
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 Cell
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 Cell
End Sub


Just select the range to fix and run Gord's macro for the reference style you
want.

 

[Gord Dibben]

Where did my "End If's" get to<g>


Gord

Don't change those values.

But Gord had a couple of typos:

Option Explicit
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 Cell
End Sub
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 Cell
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 Cell
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 Cell
End Sub


Just select the range to fix and run Gord's macro for the reference style you
want.

 

[Dave Peterson]

Maybe you changed from a single line If/Then (with continuation characters) to
block If/then/endif's???

(Or just copied from a old, old, old version???? <vbg>)

 

[Gord Dibben]

The latter<g>

Maybe you changed from a single line If/Then (with continuation characters) to
block If/then/endif's???

(Or just copied from a old, old, old version???? <vbg>)