Need help with VBA Centering The Screen On A Cell

[KNEPHK]

HI!

So i have a probleme I can not solve.


I have a spreadsheet where cell A1 always changes its value.

From A3 to A102, there is a list of numbers from 1 to 100. I would like the value of A1 to be found in the range of A3: A100 and so the screen center on that specific cell.


I found a very similar problem to mine and this was the solution:

Sub CenterOnCell(OnCell As Range)

Dim VisRows As Integer
Dim VisCols As Integer

Application.ScreenUpdating = False
'
' Switch over to the OnCell's workbook and worksheet.
'
OnCell.Parent.Parent.Activate
OnCell.Parent.Activate
'
' Get the number of visible rows and columns for the active window.
'
With ActiveWindow.VisibleRange
VisRows = .Rows.Count
VisCols = .Columns.Count
End With
'
' Now, determine what cell we need to GOTO. The GOTO method will
' place that cell reference in the upper left corner of the screen,
' so that reference needs to be VisRows/2 above and VisCols/2 columns
' to the left of the cell we want to center on. Use the MAX function
' to ensure we're not trying to GOTO a cell in row <=0 or column <=0.
'
With Application
.Goto reference:=OnCell.Parent.Cells( _
.WorksheetFunction.Max(1, OnCell.Row + _
(OnCell.Rows.Count / 2) - (VisRows / 2)), _
.WorksheetFunction.Max(1, OnCell.Column + _
(OnCell.Columns.Count / 2) - _
.WorksheetFunction.RoundDown((VisCols / 2), 0))), _
scroll:=True
End With

OnCell.Select
Application.ScreenUpdating = True

End Sub

It didn't work for me.

Can anyone help me please?

Thank you for your time!