Syntax question

[jboles]

Anyone know the syntax for accessing all formulas in a sheet. I have a sheet full of look up formulas that relies on a data range within a spearate file. That separate file is always named by date. I want access all the formulas and swap the existing date with a new date. I willl more than likely add a control of some sort that captures the the date the user wants and then assign the input to a variable swap in plae of the current date variable.

No problem on the swap routine...just need to know how to access the formulas.

Thanks

 

[Mudface]

Select all your cells then click on Edit-Goto-Special, and choose the Formulas option and click OK.

 

[jboles]

I don't quite understand..are you suggesting that I record a macro to see the code? With my cells selected after using GoTo it simply selects the first cell in the range that has a formula. I need to run a VBA routine that will accept a date value from the the user via something like an Input Box and assign it to a variable so I can swap with the variable I have already assigned to represent the current date after accessing all my formulas. There must be something like:

Cells.Range.Formulas...for example

 

[Mudface]

Yes. If had recorded the macro you would have got some code which you could amend and assign to a Range variable, eg-

Sub GetAllFormulas()
Dim rngFormulas As Range

Set rngFormulas = Cells.SpecialCells(xlCellTypeFormulas, 23)
rngFormulas.Select 'Do your stuff here, no need to Select

End Sub

Just work with the rngFormulas variable as you would do with a normal Range object.

 

[earlyd]

May not work for you, but have you considered using the substitute function?

 

[jboles]

Thanks.. I am not familiar with the substitute function

 

[earlyd]

SUBSTITUTE(text,old_text,new_text,instance_num)

Text is the text or the reference to a cell containing text for which you want to substitute characters.

Old_text is the text you want to replace.

New_text is the text you want to replace old_text with.

Instance_num specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

If you can refer to your dates as text, this could save you from having to write a macro.