Converter uma formula em VBA

Question

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)))))))))))))))))

Answer 1

Copie-a para uma célula e mantenha essa célula como ativa. Acione o Gravador de Macro. Clique na Barra de Fórmulas ou clique F2 para editar a célula. Aperte Enter. Vá até o Editor e obtenha sua Macro da fórmula.

Abraço.

---

Filipe Magno

Answer 2

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

Answer 3

Você poderia enviar a planilha ?

Uma sugestão nomeie a região ao invés de mostrar o range.

Isso facilita na leitura do código.

Att,

Thiago 

Answer 4

É uma pergunta de resposta um pouco óbvia, mas: sua fórmula funciona na planilha? Além disso, vc gravou a Macro na planilha original?

Aproveitando, há realmente necessidade de aninhar tantos "SE"? O que pretende fazer?

Abraço.

---

Filipe Magno

Answer 5

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.

Answer 6

enviar a planilha, tem como sim, não sei como? não achei nada para inserir

Answer 7

Rogger,

para enviar a planilha vc deve usar algum serviço online, como sua própria conta no OneDrive e colar o link aqui. É bastante simples.

Se não conseguir avisa aí.

---

Filipe Magno

Answer 8

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