Excel adicionando automaticamente uma linha extra com fórmulas para o número de anos inserido

Question

Estou procurando construir um modelo. Eu gostaria de ter uma página de entrada onde você insira o intervalo de anos que você gostaria de cobrir. Dependendo do número de anos inserido, desejo que uma tabela (em uma planilha diferente) seja preenchida com as fórmulas existentes.

Para ser mais específico, eu tenho uma tabela cheia de fórmulas que tem uma linha para cada ano, variando de 2005-2014.
Minha solicitação é a seguinte:

Existe uma maneira de criar uma página de entrada que inclua os anos e altere a tabela e adicione / subtraia linhas com base no intervalo inserido? Por exemplo, alteramos o intervalo na página de entrada para 2005-2018 e, em seguida, minha tabela adicionará mais 4 linhas com as equações.

    

Answer 1

A solução
Para automatizar isso, use algum código VBA que redimensione sua tabela após atualizar os anos inicial e final.

O código
Aqui está minha implementação disso, com os anos em "Sheet1" (células B1 e B2) e a tabela em "Sheet2". Observe que você deve adicionar este código à planilha onde os anos estão, porque o código deve ser executado sempre que você alterar alguma coisa nessa planilha. Então, no meu caso, esse código é adicionado à "Folha1".

Private Sub Worksheet_change(ByVal Target As Range)
    Dim StartYear, EndYour As Range
    Dim UpdateTable As ListObject
    Dim NrOfRows, OldNrOfRows As Integer

    ' Set some ranges
    Set StartYear = Worksheets("Sheet1").Cells(1, 2)
    Set EndYear = Worksheets("Sheet1").Cells(2, 2)
    Set UpdateTable = Worksheets("Sheet2").ListObjects("Table1")

    ' Check if start or end years have changed
    If (Not Intersect(StartYear, Target) Is Nothing) Or (Not Intersect(EndYear, Target) Is Nothing) Then
        ' Store the new and old number of rows
        OldNrOfRows = UpdateTable.ListRows.Count - 1
        NrOfRows = EndYear.Value - StartYear.Value + 1

        ' Resize the table
        UpdateTable.Resize UpdateTable.Range.Resize(1 + NrOfRows)

        'Delete cells below the table if it gets smaller
        If OldNrOfRows > NrOfRows Then
            UpdateTable.Range.Offset(NrOfRows + 1, 0).Resize(OldNrOfRows - NrOfRows + 1).Delete
        End If
    End If
End Sub

Alguns pontos de explicação do código
Algumas referências a funções / métodos / propriedades da Microsoft para explicar o código:

• O Excel pode chamar um sub sempre que um valor em uma célula for mudou . Esse sub é chamado de Worksheet_change e a célula que é alterada é fornecida como um valor de intervalo.
• Eu uso a função Intersect para verificar se as células que contêm o ano de início / fim mudaram. A construção NOT-NTHING pode dificultar a leitura, mas o efeito é que o IF é inserido se NOT-NOTHING for alterado.
• Para redimensionar a tabela, eu uso o método de redimensionamento ListObject (que requer um objeto de intervalo) e o Redimensionar methode do objeto de intervalo (que requer um ou dois int's).
• Finalmente, se a tabela ficar menor, tive que excluir manualmente o conteúdo abaixo da nova tabela. Aqui, novamente, eu uso alguns deslocamento de intervalo aninhados e redimensionar intervalo funções para delete o intervalo correto .