I have a excel workbook, in which certain cells are referenced with the User
defined function names.

say cell B2 has a reference to user defined function
='C:\ExcelAddin\XLStart\ABC.xla'!getValue(B1,"OWNE R")
and B3 has a reference to user defined function
='D:\ExcelAddin\XLStart\XYZ.xla'!getValue(B1,"SALE ")

I want to replace all these cell references in the complete sheet containing
getValue(....) with only the function name.

Like reference to B2 and B3 should be like
B2 = getValue(B1,"OWNER")
B3 = getValue(B1,"SALE")

Please help.

Thanks,
Sandeep

The Search And Replace feature supports wildcards. So press Ctrl-H, type
"*!GetValue(" (without the surrrounding quotes) in the "Find what" field,
"=GetValue(" (without the surrrounding quotes) in the "Replace with" field
and press "replace all" button.

Thanks for the reply.

I want that the replaced reference for the cell should be like
1. what ever is before the function name i.e. GetValue should be removed.
- As you have added the comments, the things are working fine
2. what ever is there after the function name i.e. GetValue should be
appended as it is.
Now for each cell, the string after the function name is different.
say for B2 reference is
='C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!ge tValue(B1,"OWNER","LINE_ITEMS",B12)
and for B3 the reference is
='C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!ge tValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B12 )

I want that within single find/replace, references should be replaced as
for B2 = getValue(B1,"OWNER","LINE_ITEMS",B12)
for B3 = getValue(B1,"EXTENDED_ACCOUNT_TYPE","LINE_ITEMS",B 12)

Please help.

Thanks,
Sandeep


"Stephane Quenson" wrote:

The Search And Replace feature supports wildcards. So press Ctrl-H, type
"*!GetValue(" (without the surrrounding quotes) in the "Find what" field,
"=GetValue(" (without the surrrounding quotes) in the "Replace with" field
and press "replace all" button.

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(.

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(.

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(.