Help needed to test and modify interworkbook links

[Aaron Cooper]

I have a bunch of workbooks for which I need to test some formulas and
add $ signs to the column and row if it links to a certain tab within
the same worksheet. The name of the tab is always the same so I could
do something that checks if the 6th character is a "$" and if not
insert that character.

I know that this is possible, I just have no idea how to do it. I am
pretty new to vbs and I'm trying to learn as much as possible.

I really do not know if it is possible to have this automated to check
ever cell of every tab in every open workbook.

I hope that someone can help, I would greatly appreciate it.

Thank you in advance.

 

[Bob Phillips]

Aaron,

Are you trying to change relative formulae to absolute. If so, you could try
something like

Sub ConvertFormulae()
For Each wb In Workbooks
For Each sh In wb.Worksheets
For Each cell In sh.UsedRange
If IsFormula(cell) Then
Application.ConvertFormula cell, XlReferenceStyle,
xlRelRowAbsColumn
End If
Next cell
Next sh
Next wb
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aaron Cooper]

Bob thank you for your help, do you know how I would limit this so it
only changes formulas that link to a particular sheet to an absolute
reference?

Thank you very much for your help.

Aaron

 

[Bob Phillips]

Try this Aaron.

Sub ConvertFormulae()
For Each wb In Workbooks
For Each sh In wb.Worksheets
If sh.Name = "mySheet" Then
For Each cell In sh.UsedRange
If IsFormula(cell) Then
Application.ConvertFormula cell, _
xlReferenceStyle, _
xlRelRowAbsColumn
End If
Next cell
End If
Next sh
Next wb
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aaron Cooper]

Thanks everyone for your help. I do now have a script that works
greatly. It is also quite quick, even with 100 excel files open.