Replacing part of formula in cells

[TonyAV]

Hi Everyone

is there any good way to replace the "H13", "H17", "H21", "H25" and "H29" link in my formula on top with the FORMULA in these cells?

I've attached an image file to make it easy to understand my problem.
aaaa.jpg

For example:

I want to replace the H13 in the following formula "=SE($A5="TACO";H13" with the formula in "H13" cell (=index('[TACO.xlsx]CMVCol taco3'!D$5:D$687;match...)

SE function = IF function, I'm from Brazil and my MS Office 2016 is in portuguese.

What shoul I do, people? Do I have to use Macro and VBA? is there any unknown (for me) option for this job? any other way?

I have to repeat the process with many columns, I don't want to copy and paste formulas replacing the links, it wil take too many hours, time that I don't have.

Please, help me with it

Cheers;
Tony

[Maudibe]

Try this Tony

Please Login or Register  to view this content.

HTH,
Maud

[TonyAV]

Thank you so much indeed, Maud, but now, please, in your code, Excel recreate the formula for the "H" column. How can I repeat the process with the "I", "J", "K"... column and so on?

[jindon]

Untested, see if this works...

Please Login or Register  to view this content.

[TonyAV]

Thanks for your attention, jindon , but no, the scrip is not working.

okay, I'm sharing here a link to download an example file what I want

http://s000.tinyupload.com/?file_id=...19153819631273

In think it might help you to understand my problem.

Thanks in advance

[jindon]

In that particular case, the below is working here.

Please Login or Register  to view this content.

[TonyAV]

Awesome, however i'm not a expert at VBA, where is the part of your script that copies the content of B2, B3 and B4 cells? I'd like to be able to change it

Thanks,
Tony

[jindon]

Please Login or Register  to view this content.

The bold part is the formula in the cell without "=" sign at the beginning.

[TonyAV]

=SE($A5="TACO";G13;
SE($A5="IBGE";G17;
SE($A5="USDA";G21;
SE($A5="Rotulo";G25;
G29))))

How would be your script to replace G13, G17, G21, G25 and G29 with the formula in the cells?

G13 formula is "=ÍNDICE('[TACO.xlsx]CMVCol taco3'!D$5:D$687;CORRESP($B5;'[TACO.xlsx]CMVCol taco3'!$A$5:$A$687;0);1)*$E5/$C5"

G17 formula is "=ÍNDICE('[IBGE.xlsx]Tabela de Composição '!G$5:G$1975;CORRESP($B5;'[IBGE.xlsx]Tabela de Composição '!$A$5:$A$1975;0);1)*$E5/$C5"

G21 formula is "=ÍNDICE([USDA.xlsx]ABBREV!D$2:D$8791;CORRESP($B5;[USDA.xlsx]ABBREV!$A$2:$A$8791;0);1)*$E5/$C5"

G25 formula is "=ÍNDICE('[Rotulo.xlsx]CMVCol taco3'!D$5:D$687;CORRESP($B5;'[Rotulo.xlsx]CMVCol taco3'!$A$5:$A$687;0);1)*$E5/$C5"

G29 formula is "=ÍNDICE('[Personalizado.xlsx]CMVCol taco3'!D$5:D$687;CORRESP($B5;'[Personalizado.xlsx]CMVCol taco3'!$A$5:$A$687;0);1)*$E5/$C5"

[TonyAV]

The part "For Each r In Range("b5:h5")" of your code I understood ^^

[Maudibe]

Tony,

If Jindon's code doesn't work for you and you need me to amend my code to loop through additional columns, please send me a PIM.

Maud

[xlnitwit]

Purely as an alternative, this code assumes that your range references are to cells on the same sheet as the formula cell and that the addresses are relative, not absolute

Please Login or Register  to view this content.

[Maudibe]

Hi Tony,

If this does not meet your needs, you will need to post your file in this thread. The revisedcode will cycle though columns H to K and replace the cell reference with the actual formulas in row 30 of each column. You can easily change the start and end columns in the code.

HTH,
Maud

In a standard module:

Please Login or Register  to view this content.