Excel macro to make selected range absolute or relative reference

0 votes
Accordingly, I recently came across a macro that would let you make the selected cells' absolute or relative references (would offer you to choose from four alternatives), then update the data as necessary. Since the code was based on the active/selected cells, you could input the names of the workbook or sheet without having to change anything. I looked for it again but was unable to locate it. Any thoughts?
Oct 23 in Others by Kithuzzz
• 20,660 points
64 views

1 answer to this question.

0 votes

Try  this:

Sub ConverReferenceType()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to covert reference type:", "ConvertReferenceType", myRange.Address, Type:=8)
    Set myRange = myRange.SpecialCells(xlCellTypeFormulas)
     myIndex = Application.InputBox("Select a reference type from below list:" & Chr(13) & Chr(13) _
    & "Absolute = 1" & Chr(13) _
    & "Row absolute = 2" & Chr(13) _
    & "Column absolute = 3" & Chr(13) _
    & "Relative = 4", "ConvertReferenceType", 1, Type:=1)
    For Each R In myRange
       R.Formula = Application.ConvertFormula(R.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, myIndex)
    Next
End Sub

If you don't want the reference box to appear you can comment out this line:

'Set myRange = Application.InputBox("Select one Range that you want to covert reference type:", "ConvertReferenceType", myRange.Address, Type:=8)
answered Oct 23 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
76 views
0 votes
1 answer

Excel offset where reference is to another worksheet

Solution:  =OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),ROW(),0,,) References: OFFSET(starting point, num of rows, num ...READ MORE

answered Oct 8 in Others by narikkadan
• 37,660 points
106 views
0 votes
1 answer

How to reference Microsoft.Office.Interop.Excel dll?

Use NuGet (VS 2013+): The NuGet package manager ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
146 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
451 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,218 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer
0 votes
1 answer

Convert csv- or Excel-file (xlsx) to kml with custom markers

With some help from the community, I ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
103 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP