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!
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!