VBA Search Range for text then run macro

Question

I'm trying to write a VBA macro (which I'll attach to a command button) which searches K7 through K13 to find "Sheet1", "Sheet2", "Sheet3", or "Sheet4" Only one answer is possible based on pre-existing If/Then statements. When it finds "Sheet1" I want it to run macro "GoToSheet1" When it finds "Sheet2" I want it to run macro "GoToSheet2" When it finds "Sheet3" I want it to run macro "GoToSheet3" When it finds "Sheet4" I want it to run macro "GoToSheet4"

Basically i have four possible conditions which could exist based on how someone answers two yes/no questions. That is what the initial if/then statements cover. However, I cannot get the VBA macro to search across the cell range K7 through K13 for any one of the four text phrases.

Try If Not Range("K7:K13").Find("Sheet1") Is Nothing Then GoToSheet1 — newacc2240
I tried Case Statements using existing forum examples, but am not sure how to change the search from a cell to a range. I'm also not sure how to get multiple outcomes (for example if it doesn't find "Sheet1" then i want it to search for "Sheet2"). — Rob1513
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$K$7" Then Select Case Target.Value Case "Sheet1" Call GoToSheet1 Case "Sheet2" Call GoToSheet2 Case "Sheet3" Call GoToSheet3 Case "Sheet4" Call GoToSheet4 End Select End If End Sub This is obviously wrong because it only searches a specific cell "K7" not the range "K7" to "K13" — Rob1513

newacc2240 newacc2240 · Accepted Answer · 2017-11-21T06:11:27

Surely you can loop thru the range K7:K13 for checking each cell values. However using Range.Find method would be a better way to do this.

Private Sub CommandButton1_Click()
    Dim lookingRange As Range
    Set lookingRange = Range("K7:K13")
    If Not lookingRange.Find(What:="Sheet1", LookIn:=xlValues) Is Nothing Then GoToSheet1: Exit Sub
    If Not lookingRange.Find(What:="Sheet2", LookIn:=xlValues) Is Nothing Then GoToSheet2: Exit Sub
    If Not lookingRange.Find(What:="Sheet3", LookIn:=xlValues) Is Nothing Then GoToSheet3: Exit Sub
    If Not lookingRange.Find(What:="Sheet4", LookIn:=xlValues) Is Nothing Then GoToSheet4: Exit Sub
    MsgBox "not found"
End Sub

VBA Search Range for text then run macro

1 Answers