Modifying cell references

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,"OWNER")
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'!getValue(B1,"OWNER","LINE_ITEMS",B12)
and for B3 the reference is
='C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!getValue(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",B12)

Please help.

Thanks,
Sandeep
 
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'!getValue(B1,"OWNER","LINE_ITEMS",B12)
B3 =
'C:\ExcelAddin\XLStart\ConsolidationAddin.xla'!getValue(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
 
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
 
I never said that you should put in the search and replace boxes what is
*after* the parenthesis of getValue(
Try simply with:
Find What: *!getValue(
Replace with: =getValue(

If you want to do it with a program, simply record a macro when you do the
Search And Replace operation, then look at the code and eventually adapt the
range and run it whenever you want.
 
Try recording a macro when you do it manually. If it works when you do it
manually, the code should work when it runs.
 
Back
Top