Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to test and modify interworkbook links
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to test and modify interworkbook links
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" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to test and modify interworkbook links
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to test and modify interworkbook links
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed to test and modify interworkbook links
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Assistance needed to test for open file in a different direct | Excel Discussion (Misc queries) | |||
Pasting formulas but not the links help needed | Excel Worksheet Functions | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
Help needed...test score statistics | Excel Discussion (Misc queries) | |||
help with links needed | Excel Programming |