Scroll sheet to find value based on another cell.

[cunningAce]

Hi All,

Hoping someone can help me with this.

I assume this will require VBA which i have little knowledge of,

In the below example. I want to automatically scroll to and select the cell of the equivalent Week number as the user inserts into B3.
So the result would be, M3 is now selected and had M3 not been visible on the current page view, for the sheet to have scrolled across to put it in view.

Thank you in advance for your time.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Search	10		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20

<tbody>
</tbody>

Sheet1​

 

[baitmaster]

right click on worksheet tab and select "view code"

Paste this in the VBA Editor when it appears

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


' only proceed if cell B3 is changed
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub


' switch events off whilst processing worksheet, to prevent accidental terminal loops later
Application.EnableEvents = False


' ignore any problems encountered, e.g. where invalid text entered - i.e. do not allow code to crash
On Error Resume Next


' create search term
Dim strSearch As String: strSearch = "Week " & Target.Value


' jump to first cell found containing search term - may need to restrict this to say Row 1, rather than all cells, and change [After:=] to start from say range("A1")
Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0


' switch events back on
Application.EnableEvents = True


End Sub

 

[cunningAce]

right click on worksheet tab and select "view code"

Paste this in the VBA Editor when it appears

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


' only proceed if cell B3 is changed
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub


' switch events off whilst processing worksheet, to prevent accidental terminal loops later
Application.EnableEvents = False


' ignore any problems encountered, e.g. where invalid text entered - i.e. do not allow code to crash
On Error Resume Next


' create search term
Dim strSearch As String: strSearch = "Week " & Target.Value


' jump to first cell found containing search term - may need to restrict this to say Row 1, rather than all cells, and change [After:=] to start from say range("A1")
Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0


' switch events back on
Application.EnableEvents = True


End Sub

Baitmaster this is great, thank you so much