Excel

TheSmallman.com

an XL ideas Lab

Dashboards VBA
  • Home
  • Dashboards
    • Tips & Tricks
    • Charts
    • Modelling
    • Infographics
    • VBA
  • Shop Dashboards
    • Power Pivot a User Guide
    • Excel Dashboard Course
    • Advanced Dashboard Course
    • Financial Modelling Course
    • Excel VBA Course
  • Blog
  • About
Menu

Excel Dashboards VBA

Street Address
City, State, Zip
Phone Number
an XL ideas Lab

Your Custom Text Here

Excel Dashboards VBA

  • Home
  • Dashboards
  • Excel Tips
    • Tips & Tricks
    • Charts
    • Modelling
    • Infographics
    • VBA
  • Shop Dashboards
  • PowerPivot
    • Power Pivot a User Guide
  • Courses
    • Excel Dashboard Course
    • Advanced Dashboard Course
    • Financial Modelling Course
    • Excel VBA Course
  • Blog
  • About

Convert Formula to Relative or Absolute

April 11, 2019 Marcus Small
Relative Reference VBA

I came across a post today which was asking for assistance to convert all the for converting all of a selected range to either relative or absolute referencing. 

Post

The poster at first tried to choose the Specialcells method (a snipers rifle if you will) but could not get it working so checked all the cells in a selected range (a shot gun by comparison).  The reason why the first method would be better is Excel will surgically concentrate on only the cells that have formula in and ignore the rest of the cells.  The latter method will check all of the cells in the selected range. 

The following are the values that need to be entered into the input box.

xlAbsolute         1   Convert to absolute row and column style.

xlAbsRowRelColumn  2   Convert to absolute row and relative column style.

xlRelRowAbsColumn  3   Convert to relative row and absolute column style.

xlRelative         4   Convert to relative row and column style (no references)

The following is the code I wrote to solve the problem.  It works nicely.

Option Explicit

Sub ConvertFormulasToAbsolute()
Dim rng As Range
Dim i As Integer

i = InputBox("Add a number between 1 & 4", "Goski")
  For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
      rng.Formula = Application.ConvertFormula(rng.Formula, 1, 1, i)
  Next
End Sub

Place the VBA code in a regular module and select the range you wish to affect.

All the very best.

Smallman

Tags Excel, VBA, Convert Range, Relative, Absolute, Inputbox
← Excel Dashboard Reports and CoursesConvert Entire Excel Workbook to Values →

Featured Posts

Excel Dashboards: Tracking a Crisis

Excel Dashboards: Tracking a Crisis
April 14, 2020

Recent Posts

Populating an Excel Table from a Range of Cells with VBA

Populating an Excel Table from a Range of Cells with VBA June 12, 2025

Fuzzy Distribution with Randbetween

Fuzzy Distribution with Randbetween May 21, 2025

Add Minimum and Maximum for Chart in Cells

Add Minimum and Maximum for Chart in Cells March 12, 2025

Inflation Over Multiple Years in a Single Cell

Inflation Over Multiple Years in a Single Cell January 10, 2025

Hubspot Dashboard

Hubspot Dashboard October 3, 2024

Monthly Dashboard With Supporting Metrics

Monthly Dashboard With Supporting Metrics September 25, 2024

Excel Show Missing Sheet Tabs

Excel Show Missing Sheet Tabs July 29, 2024

Run Macro Overnight Automatically

Run Macro Overnight Automatically June 24, 2024

Split File into Parts and Save to Directory

Split File into Parts and Save to Directory April 20, 2024

Most Popular Author

Most Popular Author December 14, 2023

 

Follow US:

 
 

MarcusSmall@thesmallman.com

 

TheSmallman.com - Making your small systems hum...
© Copyright 2013-2024 theSmallman.com All Rights Reserved.