relative-absolute

[lior03]

hello
is it possible to turn a formula cell from absolute to relative and vis versa?
maybe:

Sub convertformula()
ActiveCell.Application.convertformula(activecell,FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
End Sub

thanks

[mdmackillop]

I think in 2003 there is a Convert function, but I've got 2000 here and I don't see it. What version are you running?

[Bob Phillips]

Sub convertformula()
With ActiveCell
If .HasFormula Then
.Formula = Application.convertformula( .Formula, FromReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
End If
End With
End Sub You will have to break this up yourself, it just won't paste the breaks for me at the moment.

Edit MD: Hope the breaks are correct!

[lior03]

hello
what about the opposit...
from absolute to relative.what about toggle?
thanks

[Bob Phillips]

Edit MD: Hope the breaks are correct! Thanks Malcolm. PS I only raised this reply to show you that I really did know your name. Breaks are gone again though ... what is happening?

[Bob Phillips]

hello
what about the opposit...
from absolute to relative.what about toggle?
thanks The opposite is easy, just use ToAbsolute:=xlAbsRowRelColumn instead of ToAbsolute:=xlRelRowAbsColumn Howevere a toggle is harder, because there are four states, what is your toggle order?

[mdmackillop]

I've not had any problems with them, but I see what you mean. If it persists we can kick it upstairs.

[Bob Phillips]

My last post ws fine, that was a chunk of VBA which really gave me grief yesterday.

[Carl A]

Sub ConvertToAbsolute()
Dim c As Range
For Each c In Selection
If c.HasFormula Then
c.Formula = Application.convertformula( _
Formula:=c.Formula, _
FromReferenceStyle:=xlA1, _
ToAbsolute:=xlAbsolute, _
RelativeTo:=c)
End If
Next c
End Sub

Sub ConvertToRelative()
Dim c As Range
For Each c In Selection
If c.HasFormula Then
c.Formula = Application.convertformula( _
Formula:=c.Formula, _
FromReferenceStyle:=xlA1, _
ToAbsolute:=xlRelative, _
RelativeTo:=c)
End If
Next c
End Sub

'A simpler way is
Sub DoItSimple()

Selection.Replace What:="$", Replacement:=""

End Sub

The one provided by XLD will format it this way =SUM($A1:$A2)
This will format it this way =SUM($A$1:$A$2)

Sorry XLD I didn't read all of your post:

The opposite is easy, just use ToAbsolute:=xlAbsRowRelColumn instead of ToAbsolute:=xlRelRowAbsColumn Howevere a toggle is harder, because there are four states, what is your toggle order?

[lior03]

hello
i thank all prticipant in this thread.i took carl's work as well as xld's and turn it into a toolbar("lior") - 3 buttons for each macro.
i want thi toolbar not to be visibile at a specific range-like C3

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$3" Then
CommandBars("lior").Visible = False
Else: CommandBars("lior").Visible = True
End If
End Sub


how can i extend the area i want not to see the toolbar.i want this area to be
from A1 to C30
thanks

[mdmackillop]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C30")) Is Nothing Then
CommandBars("lior").Visible = False
Else: CommandBars("lior").Visible = True
End If
End Sub