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?
DeadGamersBr007 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
- Use
Worksheet_Changeevent code - Right-click on the sheet tab, select
View Codeand 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)
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
