Modify Chip Pearsons Center Of Screen code

[L. Howard]

The code from Subxx() on down is by Chip Pearson, it centers sheet on a cell, S50 in this case.
Sub LHK() is my attempt to assign a cell location to i and have the code center on that variable/range.
Range("F1") has a drop down with cells listed as L1,H35,K70...etc

I tried Dim i as Range and get the same failure, to wit:

Range("F1") Value = Empty
CenterOnCell Range(i) =Empty

Also how would I modify the With statement to have the cell in the upper left of the new screen?
I thought this With statement would do it but it just selects the new location off screen.

With Application
.Goto reference:=OnCell
End With

Option Explicit

Sub LHK()
Dim i As Variant
Set i = Range("F1").Value
CenterOnCell Range(i)
End Sub

Sub xx()
CenterOnCell Range("s50")
End Sub

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
'You can then call this procedure to center the screen on a cell.  For example to center the screen on S50, use
'COC Range("S50")

Thanks.

Regards,
Howard

 

[AlphaFrog]

Try something like this...

[COLOR=darkblue]Sub[/COLOR] LHK()
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    i = Range("F1").Value
    CenterOnCell Range(i)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Sub[/COLOR] LHK_Upper_Left()
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    i = Range("F1").Value
    Application.Goto Reference:=Range(i), Scroll:=[COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub

 

[L. Howard]

Thanks AlphaFrog,

Works fine!

I put this in Chips code body and that did the trick.

Application.Goto Reference:=Range(i), Scroll:=True

Many thanks,
Howard