I suppose the answer is Not, but I'll ask it anyway.
Whenever I use the macro recorder to record a macro, all the references to a cell are absolute and not relative. If I am in A1 and I move to A2, it writes
Range("A2").Select
But for a programming-illiterate such as me, it would be terribly useful if instead it said:
ActiveCell.Offset(1, 0).Select
Is there any way for the macro recorder to provide that kind of code? Any other alternative sources?
Dear All,
Please can you help?
In times gone past, I am sure that when I have used the macro recorder there was an option to choose "relative or absolute" references.
This has completely disappeared. I've searched high and low, through toolbars, menu etc but to no avail.
The only thing that has changed is that I am now using Excel 2003 - perhaps it is no longer present in this version?
Please can you advise.
Many thanks
Pete
Hello Bos ? anyone can help me, please..
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
Thank's b4
I am using a macro to autoformat a groups of cells(6x2) when a specific cell R[4]c[1] has specific information (below example, if said cell has "MERCH" in it, the group of cells should turn pink).
When i select cells q34:r39 and run the macro formula comes out as r38="MERCH". only cell q34 will change color. if the output were $R$34="Merch" the whole cluster of cells would turn.
[code]
' Macro8 Macro
'
'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=R[4]C[1]=""MERCH"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
[code]
a little background if there is an overall better way.
i am trying to format a seating chart to color code agents by what work type they support. they move around a lot and it is a lot of upkeep manually updating them all the time. I can do all of this manually but i actually have about 7-8 different colors i need to be able to change to and over 200 desks to code which means that it will take me days to complete and if somethign happens to the document all my work is lost. if i can make a macro to at least format each desk's color settings one at a time i can just run the macro 200 times which is a lot faster than manually changing the formatting code of all 200 clusters.
I need a macro that will allow make to select a range and where the formulas are absolute to make these relative
Your assistance will be most appreciated
Howard
Hello all,
I have found the following macro online which is supposed to allow me to select multiple cells and have the formulas within them change from relative to absolute references. Unfortunately the macro does not seem to work for me. Not being a VBA expert, can somebody please look at the following code and see if they can spot the error? Very much appreciated.
Sub MakeAbsoluteorRelativeFast()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RdoRange As Range
Dim i As Integer
Dim Reply As String
'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Relative row/Absolute column = 1" & Chr(13) _
& "Absolute row/Relative column = 2" & Chr(13) _
& "Absolute all = 3" & Chr(13) _
& "Relative all = 4", "OzGrid Business Applications")
'They cancelled
If Reply = "" Then Exit Sub
On Error Resume Next
'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
'determine the change type
Select Case Reply
Case 1 'Relative row/Absolute column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Next i
Case 2 'Absolute row/Relative column
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
Next i
Case 3 'Absolute all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i
Case 4 'Relative all
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next i
Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select
'Clear memory
Set RdoRange = Nothing
End Sub
I have a range of cells, with vey simple formula like
=b1
=b6
=c9
=d80
This is only one example column but there are other columns beside it. I would like to make the rows absolute in this type of range without going to every cell and entering F4 multiple times and also without using a macro.
Is there a way I can highlight the range and make it all absolute or give it mixed reference.
I know one can highlight range and enter formula in all of them at once by pressing Ctrl and then hitting enter, is there a substitute like that to change cell reference in a range
Hello all,
I am just learning VBA code and I came across the following macro:
Code:
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub
It is pretty neat--It adds a checkbox (of the "forms toolbar" type) and links it to each cell that has been selected. The check boxes when clicked turn their linked cells yellow. It really increases user-friendliness.
The only complaint I have is that the checkboxes end up being linked to absolute cell references. I want to be able to sort and filter my data and keep the checkboxes with their rows even when I move them around.
Is there any way to do this? I was hoping someone could very quickly modify the above code to produce this result.
I am also open to any other easier method that someone could suggest.
Thanks in advance,
Nick
Please, I need some help because when, inside a macro, I am writing a code using the information in a cell (A3) and, for any reason, I have to delete some rows, columns, or a need to move the information from A3 to B15. The macro script does not update the information and keeps the original A3 address.
My question is: Is it possible to convert the absolute cells into relative ones or to update the macro in order to have the new address ?.
Thanks a lot
