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?

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?

--

Dave Peterson

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



"Dave Peterson" wrote:

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?

--

Dave Peterson

Thanks for posting back.

smart.daisy wrote:

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

"Dave Peterson" wrote:

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?

--

Dave Peterson


--

Dave Peterson

Do you have to manually set the reference for VBScript.RegExp or does the
code do this dynamically?

Biff

"smart.daisy" wrote in message
...
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



"Dave Peterson" wrote:

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?

--

Dave Peterson

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...b;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default...b;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default...b;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible



Biff wrote:

Do you have to manually set the reference for VBScript.RegExp or does the
code do this dynamically?

Biff

"smart.daisy" wrote in message
...
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



"Dave Peterson" wrote:

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?

--

Dave Peterson


--

Dave Peterson

Thanks Dave. You da man!

Biff

"Dave Peterson" wrote in message
...
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...b;EN-US;244167
INFO: Writing Automation Clients for Multiple Office Versions

http://support.microsoft.com/default...b;en-us;245115
INFO: Using Early Binding and Late Binding in Automation

http://support.microsoft.com/default...b;en-us;247579
INFO: Use DISPID Binding to Automate Office Applications Whenever Possible



Biff wrote:

Do you have to manually set the reference for VBScript.RegExp or does the
code do this dynamically?

Biff

"smart.daisy" wrote in message
...
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



"Dave Peterson" wrote:

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?

--

Dave Peterson


--

Dave Peterson