Excel 2003 positioning a cell to first non frozen cell
 

Hello All: I have an Excel 2003 spreadsheet that looks like this:
A B C
1 Lastname Firstname Col1 Col2 Col3 €¦ Col111
2 Lname 1 Fname1 FZN

Excel 2003 positioning a cell to first non frozen cell
 

Hi Jack,
I find auto filter is a better way to zoom into the record.

Sub Nelson()
Dim Lrow As Long
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
Range("A2").Select
End If

ActiveSheet.Rows(1).AutoFilter
Rows(1).AutoFilter Field:=1, Criteria1:="Jack"
Rows(1).AutoFilter Field:=2, Criteria1:="Gombola"
Lrow = Cells(65536, 1).End(xlUp).Row
Range("IV" & Lrow & "").Select
'Data taken from your input box Col111
Cells(Lrow, 111).Select

End Sub

"JackGombola" wrote:

Hello All: I have an Excel 2003 spreadsheet that looks like this:
A B C
1 Lastname Firstname Col1 Col2 Col3 €¦ Col111
2 Lname 1 Fname1 FZN
.
.
.
101 Lname100 Fname100 X

Column C2 is frozen (FZN text above) so that I can scroll the columns and
rows without loosing my lastnames or firstnames in each row, or the column
headings.

I respond to an InputBox with €œJack,Gombola,Col111€� and the macro correctly
finds the right target cell as Row 101, col 111 (X marks this cell). What I
would like the macro to do next is to scroll the sheet horizontally and
vertically so that the X cell becomes positioned to the first frozen
location: FZN shown above.

Any suggestions are appreciated.
Thanks,
Jack

Excel 2003 positioning a cell to first non frozen cell
 

where = InputBox("enter a location")
Application.Goto Range(where), Scroll:=True


Gord Dibben MS Excel MVP


On Fri, 12 Jun 2009 17:49:01 -0700, JackGombola wrote:

Hello All: I have an Excel 2003 spreadsheet that looks like this:
A B C
1 Lastname Firstname Col1 Col2 Col3 … Col111
2 Lname 1 Fname1 FZN
.
.
.
101 Lname100 Fname100 X

Column C2 is frozen (FZN text above) so that I can scroll the columns and
rows without loosing my lastnames or firstnames in each row, or the column
headings.

I respond to an InputBox with “Jack,Gombola,Col111” and the macro correctly
finds the right target cell as Row 101, col 111 (X marks this cell). What I
would like the macro to do next is to scroll the sheet horizontally and
vertically so that the X cell becomes positioned to the first frozen
location: FZN shown above.

Any suggestions are appreciated.
Thanks,
Jack

Excel 2003 positioning a cell to first non frozen cell
 

Thank you all for your responses. I learned from each but have decided to go
with Allllen's solution.
I most appreciate your collaboration.
Jack

"JackGombola" wrote:

Hello All: I have an Excel 2003 spreadsheet that looks like this:
A B C
1 Lastname Firstname Col1 Col2 Col3 €¦ Col111
2 Lname 1 Fname1 FZN
.
.
.
101 Lname100 Fname100 X

Column C2 is frozen (FZN text above) so that I can scroll the columns and
rows without loosing my lastnames or firstnames in each row, or the column
headings.

I respond to an InputBox with €œJack,Gombola,Col111€� and the macro correctly
finds the right target cell as Row 101, col 111 (X marks this cell). What I
would like the macro to do next is to scroll the sheet horizontally and
vertically so that the X cell becomes positioned to the first frozen
location: FZN shown above.

Any suggestions are appreciated.
Thanks,
Jack