CODEWITHSUNDEEP
excelvba
user3783788
user3783788

Reputation: 303

VBA Like Operator Doesn't Pick Up Certain String

I'm writing some code that searches each cell for certain strings. The code I have will work perfect with every string I try, but for some reason won't pick up a web address I'm trying to search for. Here's the code:

If Cells(datanum, myDescCol).Value Like "*Design Review*" Or Cells(datanum, myDescCol).Value Like "*Design Review Protocol*" Or Cells(datanum, myDescCol).Value Like "*Review Protocol*" Then
    If Worksheets("CS-CRM Raw Data").Cells(datanum, myDescCol).Value Like "*https://extern.website.com/xyz/og/inu/dis/*" Then
        Worksheets("Request Results").Cells(reportnum, 7).Value = "Yes"
    ElseIf Cells(datanum, myDescCol).Value Like "*Not Required*" Then
        Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
        Worksheets("Request Results").Cells(reportnum, 7).Interior.Color = 65535
    Else
        Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
    End If
Else
    Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
End If

I have a variety of statements like this and they all work fine, but the If statement for the website usually gets skipped over for the Else or ElseIf, even if the cell has the string. Usually in the cell the website would look something like https://extern.website.com/xyz/og/inu/dis/5586574657465836574385, but since the numbers vary I cut off the search after dis/. Any idea why it's not getting picked up?

EDIT: I should also add when I edit the web address so that the numbers are removed, leaving https://extern.website.com/xyz/og/inu/dis/ in the cell, it still doesn't get picked up by the code.

Upvotes: 0

Views: 250

Answers (1)

Michael
Michael

Reputation: 111

Would using the InStr function work?

Snippet:

If InStr(1, Worksheets("CS-CRM Raw Data").Cells(datanum, myDescCol).Value, "https://extern.website.com/xyz/og/inu/dis/") Then

Full:

If Cells(datanum, myDescCol).Value Like "*Design Review*" Or Cells(datanum, myDescCol).Value Like "*Design Review Protocol*" Or Cells(datanum, myDescCol).Value Like "*Review Protocol*" Then
    If InStr(1, Worksheets("CS-CRM Raw Data").Cells(datanum, myDescCol).Value, "https://extern.website.com/xyz/og/inu/dis/") Then
        Worksheets("Request Results").Cells(reportnum, 7).Value = "Yes"
    ElseIf Cells(datanum, myDescCol).Value Like "*Not Required*" Then
        Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
        Worksheets("Request Results").Cells(reportnum, 7).Interior.Color = 65535
    Else
        Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
    End If
Else
    Worksheets("Request Results").Cells(reportnum, 7).Value = "No"
End If

Upvotes: 1

Related Questions