Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open & scroll to specific location based on value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open & scroll to specific location based on value
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. -- Regards, Tom Ogilvy "paul" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
open & scroll to specific location based on value
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
open & scroll to specific location based on value
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" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
open & scroll to specific location based on value
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" wrote: 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" wrote in message . .. 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" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show value from specific column based on location of active cell | Excel Discussion (Misc queries) | |||
Always print to a specific location | Excel Discussion (Misc queries) | |||
location appears in scroll tip when scrolling down instead of movi | Excel Discussion (Misc queries) | |||
Save to specific location | Excel Discussion (Misc queries) | |||
How do I color specific data series based on location on data she | Charts and Charting in Excel |