Usuário com melhor resposta
Converter uma formula em VBA

Pergunta
-
Boa Tarde!
Utilizo esta formula em uma planilha e gostaria de um VBA para ela.
alguém pode me ajudar?
=SE(ÉCÉL.VAZIA(SE($D$3=$M$37;(PROCH(O$8;ACL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$38;(PROCH(O$8;AMG!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$39;(PROCH(O$8;ATO!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$40;(PROCH(O$8;CAL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$41;(PROCH(O$8;GYN!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$42;(PROCH(O$8;ITA!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$43;(PROCH(O$8;ITB!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$44;(PROCH(O$8;ITR!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$45;(PROCH(O$8;MRB!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$46;(PROCH(O$8;MZL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$47;(PROCH(O$8;RED!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$48;(PROCH(O$8;STA!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$49;(PROCH(O$8;SEN!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$50;(PROCH(O$8;TCM!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0)))))))))))))))));"";SE($D$3=$M$37;(PROCH(O$8;ACL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$38;(PROCH(O$8;AMG!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$39;(PROCH(O$8;ATO!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$40;(PROCH(O$8;CAL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$41;(PROCH(O$8;GYN!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$42;(PROCH(O$8;ITA!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$43;(PROCH(O$8;ITB!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$44;(PROCH(O$8;ITR!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$45;(PROCH(O$8;MRB!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$46;(PROCH(O$8;MZL!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$47;(PROCH(O$8;RED!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$48;(PROCH(O$8;STA!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$49;(PROCH(O$8;SEN!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0));SE($D$3=$M$50;(PROCH(O$8;TCM!$D$18:$P$26;PROCV('Acomp. Mensal'!$B9;Plan1!$A$12:$B$19;2;0);0)))))))))))))))))
Respostas
-
Você tem que duplicar as " de sua fórmula para usa-la em VBA:
ActiveCell.Fomrula = "=IF(ISBLANK(IF($D$3=$M$37,(HLOOKUP(O$8,ACL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$38,(HLOOKUP(O$8,AMG!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$39,(HLOOKUP(O$8,ATO!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$40,(HLOOKUP(O$8,CAL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$41,(HLOOKUP(O$8,GYN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$42,(HLOOKUP(O$8,ITA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$43,(HLOOKUP(O$8,ITB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$44,(HLOOKUP(O$8,ITR!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$45,(HLOOKUP(O$8,MRB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$46,(HLOOKUP(O$8,MZL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$47,(HLOOKUP(O$8,RED! $D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$48,(HLOOKUP(O$8,STA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$49,(HLOOKUP(O$8,SEN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$50,(HLOOKUP(O$8,TCM!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0))))))))))))))))),"""",IF($D$3=$M$37,(HLOOKUP(O$8,ACL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$38,(HLOOKUP(O$8,AMG!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$39,(HLOOKUP(O$8,ATO!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$40,(HLOOKUP(O$8,CAL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$41,(HLOOKUP(O$8,GYN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$42,(HLOOKUP(O$8,ITA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$43,(HLOOKUP(O$8,ITB!$D$18:$P$26,VLOOKUP('Acom p. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$44,(HLOOKUP(O$8,ITR!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$45,(HLOOKUP(O$8,MRB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$46,(HLOOKUP(O$8,MZL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$47,(HLOOKUP(O$8,RED!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$48,(HLOOKUP(O$8,STA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$49,(HLOOKUP(O$8,SEN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$50,(HLOOKUP(O$8,TCM!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)))))))))))))))))"
Felipe Costa Gualberto - http://www.ambienteoffice.com.br
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 11 de julho de 2015 17:06
Todas as Respostas
-
-
Boa noite, Filipe!
Realizei conforme o instruído porem deu um erro de sintaxe.
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(IF(R3C4=R37C13,(HLOOKUP(R8C,ACL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R38C13,(HLOOKUP(R8C,AMG!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R39C13,(HLOOKUP(R8C,ATO!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R40C13,(HLOOKUP(R8C,CAL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R41C13,(HLOOKUP(R8C,GYN!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R42C13,(HLOOKUP(R8C,ITA!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R43C13,(HLOOKUP(R8C,ITB!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R44C13,(HLOOKUP(R8C,ITR!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R45C13,(HLOOKUP(R8C,MRB!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R46C13,(HLOOKUP(R8C,MZL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF
(R3C4=R47C13,(HLOOKUP(R8C,RED!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R48C13,(HLOOKUP(R8C,STA!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R49C13,(HLOOKUP(R8C,SEN!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R50C13,(HLOOKUP(R8C,TCM!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0))))))))))))))))),"""",IF(R3C4=R37C13,(HLOOKUP(R8C,ACL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R38C13,(HLOOKUP(R8C,AMG!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R39C13,(HLOOKUP(R8C,ATO!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R40C13,(HLOOKUP(R8C,CAL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R41C13,(HLOOKUP(R8C,GYN!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R42C13,(HLOOKUP(R8C,ITA!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3
C4=R43C13,(HLOOKUP(R8C,ITB!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R44C13,(HLOOKUP(R8C,ITR!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R45C13,(HLOOKUP(R8C,MRB!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R46C13,(HLOOKUP(R8C,MZL!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R47C13,(HLOOKUP(R8C,RED!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R48C13,(HLOOKUP(R8C,STA!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R49C13,(HLOOKUP(R8C,SEN!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)),IF(R3C4=R50C13,(HLOOKUP(R8C,TCM!R18C4:R26C16,VLOOKUP('Acomp. Mensal'!RC2,Plan1!R12C1:R19C2,2,0),0)))))))))))))))))"
Range("O10").Select
End Sub
-
-
-
Ola,
Rsrs, é muito SE, tenho uma planilha que tem um gerenciador de informação onde tenho 14 bases de informação, que conforme selecionado o nome do locar ela puxa de dentro das 14 bases "abas". não sei como reduzir a formula para deixar a planilha mais leve.
-
-
-
Você tem que duplicar as " de sua fórmula para usa-la em VBA:
ActiveCell.Fomrula = "=IF(ISBLANK(IF($D$3=$M$37,(HLOOKUP(O$8,ACL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$38,(HLOOKUP(O$8,AMG!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$39,(HLOOKUP(O$8,ATO!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$40,(HLOOKUP(O$8,CAL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$41,(HLOOKUP(O$8,GYN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$42,(HLOOKUP(O$8,ITA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$43,(HLOOKUP(O$8,ITB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$44,(HLOOKUP(O$8,ITR!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$45,(HLOOKUP(O$8,MRB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$46,(HLOOKUP(O$8,MZL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$47,(HLOOKUP(O$8,RED! $D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$48,(HLOOKUP(O$8,STA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$49,(HLOOKUP(O$8,SEN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$50,(HLOOKUP(O$8,TCM!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0))))))))))))))))),"""",IF($D$3=$M$37,(HLOOKUP(O$8,ACL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$38,(HLOOKUP(O$8,AMG!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$39,(HLOOKUP(O$8,ATO!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$40,(HLOOKUP(O$8,CAL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$41,(HLOOKUP(O$8,GYN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$42,(HLOOKUP(O$8,ITA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$43,(HLOOKUP(O$8,ITB!$D$18:$P$26,VLOOKUP('Acom p. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$44,(HLOOKUP(O$8,ITR!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$45,(HLOOKUP(O$8,MRB!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$46,(HLOOKUP(O$8,MZL!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$47,(HLOOKUP(O$8,RED!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$48,(HLOOKUP(O$8,STA!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$49,(HLOOKUP(O$8,SEN!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)),IF($D$3=$M$50,(HLOOKUP(O$8,TCM!$D$18:$P$26,VLOOKUP('Acomp. Mensal'!$B9,Plan1!$A$12:$B$19,2,0),0)))))))))))))))))"
Felipe Costa Gualberto - http://www.ambienteoffice.com.br
- Marcado como Resposta Felipe Costa GualbertoMVP, Moderator sábado, 11 de julho de 2015 17:06