By dimming the re variable as an object and using createobject(), there is
no
reference required.
This is called late binding. It has the disadvantage of being slower
(milliseconds???), but has the added advantage of not having to worry
about
specific versions of the possible references.
When developing a routine, I'd use early binding (include the reference
and dim
the variables accordingly) to be able to get the VBE's intellisense--I
find it
very useful. Then when the project is ready to release to the world,
remove the
reference (and use "As Object"--and replace any constants) and let it go.
You might want to look at the way Dick Kusleika does it:
http://www.dicks-clicks.com/excel/olBinding.htm
You may want to look at these links that Tom Ogilvy posted recently:
Here are some more extensive references on binding:
Use late binding - don't have a reference to excel.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions
http://support.microsoft.com/default.aspx?scid=kb;en-us;245115
INFO: Using Early Binding and Late Binding in Automation
http://support.microsoft.com/default.aspx?scid=kb;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible
Do you have to manually set the reference for VBScript.RegExp or does the
code do this dynamically?
Biff
smart.daisy said:
Thanks Dave. I wrote VB code. Hope it will be helpful for other guys.
Sub replace()
Dim c, re, PatternStr, ReplaceStr, LookInStr
Set re = CreateObject("VBScript.RegExp")
PatternStr = ",'Q1'!([\$A-Z0-9a-z:]+),"
With re
.Pattern = PatternStr
.Global = True
End With
ReplaceStr = ",INDIRECT(Fcst&""!$1""),"
For Each c In Selection
LookInStr = c.Formula
c.Formula = re.replace(LookInStr, ReplaceStr)
Next c
End Sub
:
Maybe you can do it in pieces...
First change the formulas to text
Select the range with the formulas
edit|Replace
what: = (equal sign)
with: $$$$$
replace all
Then do the first change
edit|replace
what: 'sample'
with: indirect(fcst&"!"
replace all
Maybe with should be:
indirect("'"&fcst&"'!"
???
then once more (or multiple times more):
edit|replace
what: ,2,false)
with: ),2,false)
replace all
(If you have formulas that bring back other columns, you'll want to
repeat this
step:
edit|replace
what: ,3,false)
with: ),3,false)
replace all
As many times as necessary.
Then change your text back to formulas:
edit|replace
what: $$$$$
with: =
replace all
I'd only do a few cells to make sure the formulas were ok. Then
select
the
remainder of the range and replace all.
smart.daisy wrote:
Hi,
I have dozens of formula like this:
VLOOKUP($A22,'sample'!$A$302:$Z$302,2,FALSE), except $A$302:$Z$302
might
change like $A$50:$Z$51, picking up from different area.
Now I want to replace above formula like this:
VLOOKUP($G13,INDIRECT(Fcst&"!$b$20:$z$30"),2,FALSE) but I won't
change
area.
Because there are so many formula, I want to change them in a batch,
I
try to
use replace (ctrl+R), but it doesn't work because I only want to
replace
'sample'!$ with INDIRECT(Fcst&"!
I got error message because I can't replace including ")
Is there a way to change this formula in a batch?
