Guru do Excel e Power BI

Pessoal,

Estou precisando rodar uma fórmula com array em varias células por mês, porém ela está deixando minha planilha extremamente lenta. Então pensei em gerar uma macro que rode a fórmula apenas nas células do mês específico, no caso hoje-1, ou então somente nas células selecionadas. É possivel?

formula que eu preciso rodar: =SEERRO(ÍNDICE(Relatorio!$A:$J;CORRESP($B$34&F$8;Relatorio!$A:$A&Relatorio!$C:$C;0);CORRESP($A$28;Relatorio!$A$1:$J$1;0));"")

Kayteas escreveu: ... porém ela está deixando minha planilha extremamente lenta.
Na sua fórmula há referências a colunas inteiras ($A:$J, $A:$A, $C:$C) e isso provavelmente é o que mais contribui para a lentidão do recálculo.
Outro fator que contribui é a quantidade de fórmulas. Se há intervalos em que os valores não mais serão alterados, por exemplo de meses passados, e caso ainda existam fórmulas, você pode eliminar as fórmulas substituindo-as pelos seus respectivos valores ~~~> Copiar / Colar Especial / Valores.

Então pensei em gerar uma macro que rode a fórmula ... somente nas células selecionadas. É possivel?
A sua fórmula possui somente um endereço relativo "F$8" então eu suponho que ela seja inserida em linha. Se sim, selecione o intervalo em linha, mínimo duas células, exemplos D5:E5 ou K10:T10, e rode o código abaixo.

Código: Selecionar todos

Sub InsereFórmulaNaSeleção()
 With Selection
  .Cells(1).FormulaArray = "=IFERROR(INDEX(Relatorio!$A:$J,MATCH($B$34&F$8,Relatorio!$A:$A&Relatorio!$C:$C,0),MATCH($A$28,Relatorio!$A$1:$J$1,0)),"""")"
  .Cells(1).AutoFill Destination:=.Cells(1).Resize(, .Cells.Count)
 End With
End Sub

obs. se você selecionar um intervalo de por exemplo 10 células em linha poderá observar a lentidão do código, pois a cada fórmula inserida a planilha recalcula. Substitua na fórmula as referências a colunas inteiras e você poderá observar que o código será menos lento. Eu não setei para cálculo manual justamente para observar essa diferença.

Osvaldo, muito obrigada pelo retorno!

Na verdade, F8 representa o mes, entao todo mes eu precisaria mudar esta condição. As celulas que eu precisaria selecionar são nesta mesma coluna (F), no caso as linhas de 28 a 31 . Veja um exemplo anexo. Este mes estou trabalhando em valores de julho (F8) e preciso rodar a formula nesta coluna (linhas de 28 a 31). Mes que vem vai ser na coluna G e assim sucessivamente.

PS: eu tentei fazer conforme sua primeira sugestao, copiando e colando as celulas estaticas, mas ainda assim ficou lento.

Se puder dar uma olhadinha no meu exemplo e me ajudar, ficarei imensamente grata! Estou quebrando a cabeça pra entregar este relatorio! rsrs

Kayteas escreveu:As celulas que eu precisaria selecionar são nesta mesma coluna (F), no caso as linhas de 28 a 31 . Este mes estou trabalhando em valores de julho (F8) e preciso rodar a formula nesta coluna (linhas de 28 a 31). Mes que vem vai ser na coluna G e assim sucessivamente.

Como eu comentei antes a única referência relativa é F$8, isto implica que a sua fórmula aplicada nas linhas 28 a 31 de qualquer coluna retornará resultados iguais em todas as células, por isso não vejo sentido em estender a fórmula em coluna. Ela só vai se alterar se estendida em linha. Por exemplo se a fórmula for inserida em A28 e for arrastada (ou copiada/colada) para B28, aí sim F$8 será alterada para G$8.

Eu não sei o que você está tentando calcular com essa fórmula, mas em relação a inserir a fórmula via macro, que é o foco deste tópico, segue abaixo a opção de inserir em coluna, como é a sua pretensão.

substitua esta linha

Código: Selecionar todos

.Cells(1).AutoFill Destination:=.Cells(1).Resize(, .Cells.Count)

por esta

Código: Selecionar todos

.Cells(1).AutoFill Destination:=.Cells(1).Resize(.Cells.Count)

Selecione F28:F31 e rode o código. As fórmulas inseridas retornarão resultados iguais.

Oi Osvaldo! Agora entendi o que voce falou... É que tinha um erro na minha formula, la no A28, ela é relativa, fica $A28 e nao $A$28, pq ai ela desce para $A29 e assim por diante... Ja corrigi no codigo que vc me enviou...

Agora, com relação a linha 8 (F8), na verdade ela precisaria variar de acordo com a coluna que eu selecionar. Na formula do jeito que está, por mais q eu selecione a coluna G ou qualquer outra, ela continua buscando o mes de F8, e eu gostaria que, se eu selecionar G28:G31, ele busque o mes de G8; se eu selecionar M28:M31, ele busque o mes de M8, e por assim vai... É possivel?

Agora entendi a questão do F$8. Veja se o código abaixo atende.
Alterei de $A$28 para $A28 e no lugar do F de F$8 entra a letra correspondente à coluna em que a fórmula for inserida.

Código: Selecionar todos

Sub InsereFórmulaNaSeleção()
 With Selection
  .Cells(1).FormulaArray = "=IFERROR(INDEX(Relatorio!$A:$J,MATCH($B$34&" & Cells(8, ActiveCell.Column).Address(, 0) & ",Relatorio!$A:$A&Relatorio!$C:$C,0),MATCH($A28,Relatorio!$A$1:$J$1,0)),"""")"
  .Cells(1).AutoFill Destination:=.Cells(1).Resize(.Cells.Count)
 End With
End Sub

Muito obrigada Osvaldo! Arrasou! Fiquei muitissimo feliz com o resultado!

Guru do Excel e Power BI

Macro pra rodar fórmula em célula específica

Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica

Re: Macro pra rodar fórmula em célula específica