Try recording a macro when you do it manually. If it works when you do it
manually, the code should work when it runs.

Sandeep wrote:

So, in short, instead of performing 2 steps we should be able to update the
references within one steps itself.

I need to do this programmtically in VBA

Thanks,
Sandeep

"Sandeep" wrote:

Say ,
B2 =
'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"OWNER","LINE_ITEMS",B12)
B3 =
'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!get Value(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12)

You see, the parameters passed to the function are different for both these
cells

Now to replace the things,
First, I need to do the CTRL+H where
- FIND value - "*getValue(B1,"OWNER","LINE_ITEMS",B12)"
REPLACE with value - "=getValue(B1,"OWNER","LINE_ITEMS",B12)"
This will update the B2 reference as required.

Second, Again i need to do the CTRL+H where
- FIND value - "*getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)"
REPLACE with value -
"=getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS" ,B12)"
This will update the B3 reference as required.

So you see, we need to make do the same thing twice, two update the
references as the parameters to the same function are different.
I want is that using single replace, i can perform update both B2 and B3 at
the same time.

Something like this:
- FIND value - "*getValue(XXX)"
REPLACE with value - "=getValue(XXX)"

what ever value (here XXX) comes after the function name(here getValue)
should be appended to the replace value as it is.

Please help.

Thanks,
Sandeep

"Stephane Quenson" wrote:

Not sure I get your point. The Search And Replace I gave you removes
everything before getValue(, and keeps everything as it is after getValue(.



--

Dave Peterson