Vba code to backup a cell formula - chr(34) does not work

[benabrink]

Hi there,


I am trying to backup a formula with a VBA code, i have already tried many combinations but nothing works.
Could you help me, idk what i'm doing wrong ...


Just to context, the entire process is to replace the formula (linked to 3 different sheets) replacing the "#REF" caused by delete these sheets and import them again.

Formula:

=IF(IF(OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1);VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&"_"&$G1;Padrao!$A:$E;5;FALSE))="MR";VLOOKUP($G1;Up_lw!$C:$E;3;FALSE);IF(OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1);VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&"_"&$G1;Padrao!$A:$E;5;FALSE)))

if i try to use the code bellow, the VB returns an error as "Application-define or object-define error"

teste = "=IF(IF(OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1);VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&'_'&$G1;Padrao!$A:$E;5;FALSE))='MR';VLOOKUP($G1;Up_lw!$C:$E;3;FALSE);IF(OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1);VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&'_'&$G1;Padrao!$A:$E;5;FALSE)))"

Range("form_cluster_unico").Formula = Replace(teste, "'", Chr(34))

Regards

 

[njimack]

You've replaced quote marks with apostrophe's when you should have used double quotes. Do you still get the error if you replace
'Mr'
with
""Mr""

Also, you could condense your formula so that this:

OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;  201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1)

becomes this:

OR(AND($C1>=201501,$C1<=201512),AND($C1>=201601,$C1<=201603))

 

[benabrink]

You've replaced quote marks with apostrophe's when you should have used double quotes. Do you still get the error if you replace
'Mr'
with
""Mr""

Also, you could condense your formula so that this:

OR(201501=$C1;201502=$C1;201503=$C1;201504=$C1;201505=$C1;201506=$C1;201507=$C1;201508=$C1;201509=$C1;201510=$C1;  201511=$C1;201512=$C1;201601=$C1;201602=$C1;201603=$C1)

becomes this:

OR(AND($C1>=201501,$C1<=201512),AND($C1>=201601,$C1<=201603))

njimack, ty for the sugestion !!
i´ll condense it, but about the use of double quotes, it still not working

 

[njimack]

njimack, ty for the sugestion !!
i´ll condense it, but about the use of double quotes, it still not working

You need to replace every apostrophe within the formula, not just the example I gave.

 

[benabrink]

njimack, ty for the sugestion !!
i´ll condense it, but about the use of double quotes, it still not working

now the formula is:

=IF(IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&"_"&$G1;Padrao!$A:$E;5;FALSE))="MR";VLOOKUP($G1;Up_lw!$C:$E;3;FALSE);IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&"_"&$G1;Padrao!$A:$E;5;FALSE)))

But it does not work in VB Code (When i use the syntax "Range("XYZ").Formula = code above"

 

[benabrink]

You need to replace every apostrophe within the formula, not just the example I gave.

You are saying to do like this:

Range("XYZ").Formula = "=IF(IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&""_""&$G1;Padrao!$A:$E;5;FALSE))=""MR"";VLOOKUP($G1;Up_lw!$C:$E;3;FALSE);IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&""_""&$G1;Padrao!$A:$E;5;FALSE)))"

right ?
but it does not work

 

[njimack]

I can't test this because I have different regional settings to you, but the below should work:

Range("A1").Formula = "=IF(IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&""_""&$G1;Padrao!$A:$E;5;FALSE))=""MR"";VLOOKUP($G1;Up_lw!$C:$E;3;FALSE);IF(OR(AND($C1>=201501;$C1<=201512);AND($C1>=201601;$C1<=201603));VLOOKUP($G1;mar!$C:$E;3;FALSE);VLOOKUP($C1&""_""&$G1;Padrao!$A:$E;5;FALSE)))"

 

[steve the fish]

I believe VBA requires the use of commas not semicolons.

 

[benabrink]

I believe VBA requires the use of commas not semicolons.

PERFECT !!!!

That´s it !!!

Great !!!

Ty steve the fish, and njimack !!!

Good job !!