Changing multiple cell references to absolute references within formula

[JWill78]

Hi

I have a row of cells each with formula along the lines of: =A1xB1xInputs!A1xInputs!B1

What I'd like to do is convert all of the references to the Inputs sheet in each cell to absolute references, but leave the others as relative references. I have VBA that will convert all references to absolute/relative but other than doing a separate find and replace for each individual reference to the Input sheet I can't figure out how to quickly change just those references for all selected cells.

All help greatly appreciated!

 

[JWill78]

Have found a solution elsewhere, posted below in case be of use to others:

Sub ConvertInputsToAbsoluteReferences()
Dim cell As Range
Dim formula As String
Dim regex As Object
Dim matches As Object
Dim match As Object
Dim newFormula As String

' Create a new regular expression object
Set regex = CreateObject("VBScript.RegExp")

' Pattern to match references like Inputs!A1, Inputs!Z99, etc.
regex.Pattern = "Inputs!\$?[A-Z]+\$?[0-9]+"
regex.Global = True

' Loop through all selected cells
For Each cell In Selection
If cell.HasFormula Then
formula = cell.Formula

' Find all matches of Inputs! references in the formula
Set matches = regex.Execute(formula)

' Start with the original formula
newFormula = formula

' Loop through each match found
For Each match In matches
' Convert each matched reference to an absolute reference
newFormula = Replace(newFormula, match.Value, Application.ConvertFormula(match.Value, xlA1, xlA1, xlAbsolute))
Next match

' Update the formula in the cell
cell.Formula = newFormula
End If
Next cell
End Sub