CODEWITHSUNDEEP
vbaexcel
Rob1513
Rob1513

Reputation: 3

VBA Search Range for text then run macro

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.

Upvotes: 0

Views: 213

Answers (1)

newacc2240
newacc2240

Reputation: 1425

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

Upvotes: 0

Related Questions