Como atualizar referências de célula ao configurar a propriedade Formula no VBA?

Question

Aqui está um trecho do meu código:

 For i = 1 To k        
    If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then
   Else
      Range("D2").Offset(i, 0).Formula = Range("D2").Formula

A fórmula em D2 é

   =IF(F2<0,6;5;IF(F2<0,9;10;22))

O problema é que todas as células na coluna D obtêm a mesma fórmula. Como posso alterá-lo para que ele se ajuste automaticamente como na interface do Excel (por exemplo, F2 se torna F3 e assim por diante)

    

Answer 1

A propriedade de fórmula usa a fórmula exatamente conforme especificado. Não sabe nada sobre o ajuste das referências. Você precisa nos manipular com strings para corrigir a fórmula copiada antes de configurá-la na nova célula.

Talvez seja melhor apenas criar a fórmula no código em vez de copiá-la e tentar consertá-la.

    

Answer 2

Se houver um motivo específico para usar a funcionalidade de autopreenchimento do Excel (talvez você não saiba o que a fórmula será e deseje manter seu código simples), você pode usar a função Filldown .

Você precisa garantir, no entanto, que o modo de cálculo ( CalcMode ) esteja definido como automático ( xlCalculationAutomatic ).

Range(Range("D2").Address & ":" & Range("D2").Offset(k, 0).Address).FillDown

Observação: o código acima deve ser retirado do loop for .

    

Answer 3

Você seria mais bem servido dividindo a função IF da célula e codificando-a dentro do VBA.

Se eu entendi a sua função IF corretamente (percebo que você não está usando uma versão em inglês usando o modelo de pontuação), é algo parecido com isso no VBA:

If Range("D2").Offset(i, 2) < 0.6 Then
    Range("D2").Offset(i, 0) = 5
    ElseIf Range("D2").Offset(i, 2) < 0.9 Then
        Range("D2").Offset(i, 0) = 10
        Else
            Range("D2").Offset(i, 0) = 22
End If

Isso substituiria essa linha em seu código:

Range("D2").Offset(i, 0).Formula = Range("D2").Formula

Estou usando deslocamento (i, 2) para se referir à coluna consultada em F2.