CODEWITHSUNDEEP
excelvbaif-statement
JoshL
JoshL

Reputation: 164

Searching for a specific text in a column within an If statement

I am trying to find a specific text within a column using an if statement (shown below). Currently I'm searching each cell individually but would like to be able to shorten that code to search all of them.

If InStr(Sheet2.Range("R" & CustRow).Value, "Cradle") > 0 Then
    If InStr(Sheet2.Range("R5"), "PFC") Or
    If InStr(Sheet2.Range("R6"), "PFC") Or
    If InStr(Sheet2.Range("R7"), "PFC") Or
    If InStr(Sheet2.Range("R8"), "PFC") Or
    If InStr(Sheet2.Range("R9"), "PFC") Or
    If InStr(Sheet2.Range("R10"), "PFC") Or
    If InStr(Sheet2.Range("R11"), "PFC") Or
    ....
 Then
    ThisWorkbook.FollowHyperlink PdfTemplate16
Else: ThisWorkbook.FollowHyperlink PDFTemplateFile17
Application.Wait Now + 0.00001
End If

I would like to be able to search from row 5 to row 44 but really don't want to have that repetitive code in there.

Thank you for any help.

Upvotes: 0

Views: 62

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

You can put the together and just text once:

Sub poiuyt()
    With Application.WorksheetFunction
        s = Join(.Transpose(Sheet2.Range("R5:R44").Value), Chr(1))
        If InStr(s, "PFC") > 0 Then
            MsgBox "in there somewhere"
        Else
            MsgBox "not in there anywhere"
        End If
    End With
End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Use Range.Find, and test if the Find actually succeeded to then take one action or another, something like the following:

Dim rangeToFind as Range
Set rangeToFind = Sheet2.Range("R5:R44").Find(What:="PFC", LookIn:=xlValues, LookAt:=xlPart)

If Not rangeToFind Is Nothing Then
   ' found a PFC cell, do thing A
Else
   ' no cell found, do thing B
End If

Upvotes: 1

Related Questions