open & scroll to specific location based on value

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

Hello Gurus and News Group users.

You’re kind assistance please.

I am trying to open a worksheet at specific location. Not
a cell reference but the first cell that has a specific
value.

The value is within a specific column (column 2) but the
row location does vary.

I have looked at the Goto method, but this seems to look
at specific cell references.

Any help would be appreciated.

PW
 
Private Sub Workbook_Open()
Dim rng as Range
with ThisWorkbook.Worksheets(1)
set rng = .Columns(2).Find(What:="ABCD", _
After:=.Range("B65536"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If not rng is nothing then
Application.Goto rng, True
Else
Application.Goto .Range("B1"), True
End if
End With
End Sub

You might have to change some of the parameter settings so it finds you
search target (if it is produced by a formula, then you would change LookIn
to xlValues rather than xlFormulas, as an example). Change What:="ABCD" to
your target value.

Put this in the ThisWorkbook module.
 
Paul, try:

in thisworkbook code

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto [sheet1!b1].EntireColumn.Find("hmm")
If Err <> 0 Then Beep
End Sub

or alternatively in separate module
Sub Auto_Open()


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
to the Original Poster,
Just a word of caution,

While this looks "Cool", failure to set your parameters for the Find command
may result in unexpected results.

several of these parameters are persistent and may reflect selections made
manually or by code in previous usage.


If I put
="hm" & "m" in column B and run Find with xlFormulas, it is not found,
then running this code

Application.Goto [sheet1!b1].EntireColumn.Find("hmm")

also fails.

While
Application.Goto [sheet1!b1].EntireColumn.Find("hmm", Lookin:=xlValues)

succeeds.

From help on the Find method:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you don't specify values for these arguments
the next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if
you omit the arguments. To avoid problems, set these arguments explicitly
each time you use this method.

--

Regards,
Tom Ogilvy


keepitcool said:
Paul, try:

in thisworkbook code

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto [sheet1!b1].EntireColumn.Find("hmm")
If Err <> 0 Then Beep
End Sub

or alternatively in separate module
Sub Auto_Open()


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


paul said:
Hello Gurus and News Group users.

You’re kind assistance please.

I am trying to open a worksheet at specific location. Not
a cell reference but the first cell that has a specific
value.

The value is within a specific column (column 2) but the
row location does vary.

I have looked at the Goto method, but this seems to look
at specific cell references.

Any help would be appreciated.

PW
Click to expand...
Click to expand...
 
Paul, Tom

Another solution :
a: avoids the find method and its persistent settings
b: searches values so "hm"&"m" and hmm are found
c: is case INsensitive

Sub Workbook_Open()
On Error Resume Next
With [sheet1!B:B]
Application.Goto .Cells(WorksheetFunction.Match("hmm", .Cells, 0), 1)
If Err <> 0 Then Beep: Application.Goto .Cells(1)
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Paul said:
Thank you Tom for your time. It is very appreciated

Regards
PaulW
-----Original Message-----
to the Original Poster,
Just a word of caution,

While this looks "Cool", failure to set your parameters for the Find command
may result in unexpected results.

several of these parameters are persistent and may reflect selections made
manually or by code in previous usage.


If I put
="hm" & "m" in column B and run Find with xlFormulas, it is not found,
then running this code

Application.Goto [sheet1!b1].EntireColumn.Find("hmm")

also fails.

While
Application.Goto [sheet1!b1].EntireColumn.Find("hmm", Lookin:=xlValues)

succeeds.

From help on the Find method:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you don't specify values for these arguments
the next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if
you omit the arguments. To avoid problems, set these arguments explicitly
each time you use this method.

--

Regards,
Tom Ogilvy


keepitcool said:
Paul, try:

in thisworkbook code

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto [sheet1!b1].EntireColumn.Find("hmm")
If Err <> 0 Then Beep
End Sub

or alternatively in separate module
Sub Auto_Open()


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >



Hello Gurus and News Group users.

You’re kind assistance please.

I am trying to open a worksheet at specific location. Not
a cell reference but the first cell that has a specific
value.

The value is within a specific column (column 2) but the
row location does vary.

I have looked at the Goto method, but this seems to look
at specific cell references.

Any help would be appreciated.

PW
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top