A function that changes X cells

Question

So, im trying to make a function that updates automatically whenever i change manually the value of a cell. It should work like this:The MAX value defines the max value of this list

and whenever i change it, the list changes too

I could do it with a Sub Process, but it only updates manually. So i thought i could just make it a function and use it in a random cell and it would update automatically, but it didn't work:

Function lista_auto(n)

For i = 2 To n
Range("E" & i) = i - 1

listaauto = Null

End Function

Does anyone have a solution to this? Or is it just impossible?

Answer 1

• Use Worksheet_Change event code to populate cells on Col E
• Right-click on the sheet tab, select View Code and paste the code into the code window

Microsoft documentation:

Range.Offset property (Excel)

Range.Resize property (Excel)

IsNumeric function

Worksheet.Change event (Excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastCell As Range, iVal As Long, tVal As Long
    Const START_CELL = "E2"
    If Target.Address(0, 0) = "C2" Then
        tVal = Target.Value
        If Not VBA.IsNumeric(tVal) Then Exit Sub
        Set lastCell = Range("E" & Me.Rows.Count).End(xlUp)
        With Me.Range(START_CELL)
            If lastCell.Row < .Row And .Row > 1 Then
                Set lastCell = .Offset(-1)
            End If
        End With
        iVal = lastCell.Value
        If Len(tVal) = 0 Then
            Me.Range(START_CELL, lastCell).ClearContents
        End If
        If iVal = tVal Then Exit Sub
        Application.EnableEvents = False
        If iVal < tVal Then
            With lastCell.Offset(1).Resize(tVal - iVal)
                .Formula = "=Row()-" & Range(START_CELL).Row - 1
                .Value = .Value
            End With
        Else
            lastCell.Offset(tVal - iVal + 1).Resize(iVal - tVal).ClearContents
        End If
    End If
    Application.EnableEvents = True
End Sub