DO loop with an IF statement

[PiR-KU]

Dear all,

I am having an issue getting an IF statement to work inside a DO loop and I was wondering if you would be so kind as to give me some indication as to why it is not working. I have Microsoft Excel 2007 and Windows 7 Entreprise.

In my Excel spreadsheet, I have in column B a list of keywords and in column C the category to which each keyword belongs. For instance, the keyword "boot" goes withe the category "Shoes", "scarf" goes in "Accessories", etc.

In another column, I have a list of product descriptions (like "Black Boots" or "Silk Scarf") and I would like to assign the relevant category to each product. I thought to do this by creating a new function in VBA. This function would go through the whole list of keywords in column B, checking if the keyword is included in the product description. If it is, the function's result would be the relevant category, and if it is not, the function would search the next line in column B, until a relevant keyword is found, or until the end of the list.

Here is the code I have written so far:

Function SegmentSearch(Item)
    Dim i As Integer
    i = 1
    Do
        i = i + 1
        If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE" Then SegmentSearch = Cells(i, 3)
    Loop Until Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE"            
End Function

Without the loop, the function works fine (if "Item" happens to include the first keyword in the list) but as soon as I try putting the loop I get a #VALUE! error.

I thank you in advance for your help on this topic.

Kind regards,
Pierre

 

[mikerickson]

You don't mention what "doesn't work" means, what behaviour does the loop create

First, there is a VBA function IsNumeric that can be used instead of the WorksheetFunction.IsNumber.

Second, the =TRUE is redundant.

 If ("A" = "B") = True Then
'is the same as 
If ("A"="B") Then

Third, the Cells(i,3) is unqualified


try

Function SegmentSearch(Item)
    Dim i As Integer
    i = 1
    Do
        i = i + 1
        If IsNumeric(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item))  Then 
            SegmentSearch = Sheet5.Cells(i, 3)
        End If
    Loop Until IsNumeric (Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item))         
End Function

you also might use Exit Do.

 

[jamcall]

Tried playing with this on XL2007... When Search returns #VALUE it exits out of the function...

The following seems to work around this behavior:

Public Function SegmentSearch(Item)
    On Error Resume Next
    Dim i As Long
    Dim lastRow As Long
    Dim x As Integer
    'Determine # of items in list
    lastRow = Range("B65536").End(xlUp).Row
    For i = 2 To lastRow ' First row is header
        x = 0   ' Initialize x
        
        ' if Search throws error (value not found) x won't change
        x = Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)
        If x <> 0 Then
            SegmentSearch = Cells(i, 3)
            Exit For ' Done
        End If
    Next i
End Function

HTH,
~ Jim

 

[mikerickson]

Try Application.Search instead of Application.WorksheetFunction.Search.

Application.Search("a", "xyz") returns the error value, cvErr(xlErrValue), which VBA can handle with IsNumeric or IsError.

Application.WorksheetFunction("a", "xyz") causes a VBA error that has to be handled with an On Error statement.

Alternatly, you could use the VBA function InStr.

MsgBox InStr(1, "abc", "b"): Rem 2
MsgBox InStr(1, "abc", "X"): Rem 0

 

[PiR-KU]

Dear sir,

Thank you very much for your quick answer.

I'm sorry I wasn't precise enough in my first post, the "doesn't work" means that when I use the formula in the Excel spreadsheet, all I get is a "#VALUE!" error message in the cell. No error message on the VBA side. Unfortunately, I do not have any more information... I believe it comes from an error in the "Search" function (it cannot find the value it is looking for).

The code you have given me only works if the product description contains the keyword in the line of the first value of i (i.e. the value in line 2 in the present case), which leads me to think that either (1) the loop is not going onto the next iterations, or (2) the SegmentSearch function always keeps it's very first value, whatever the result in the next iterations. Do you think this may be the issue here?

Once again, thank you for your help, and sorry for the inconvenience.

Kind regards,
Pierre

 

[jamcall]

Try Application.Search instead of Application.WorksheetFunction.Search.

I'm not seeing Search as an available method for Application; is there a reference that needs to be added for that?

Sorry to hijack your thread Pierre - hopefully you'll find something useful in our discussion...

Cheers,
~ Jim

 

[PiR-KU]

Tried playing with this on XL2007... When Search returns #VALUE it exits out of the function...

The following seems to work around this behavior:

Public Function SegmentSearch(Item)
    On Error Resume Next
    Dim i As Long
    Dim lastRow As Long
    Dim x As Integer
    'Determine # of items in list
    lastRow = Range("B65536").End(xlUp).Row
    For i = 2 To lastRow ' First row is header
        x = 0   ' Initialize x
        
        ' if Search throws error (value not found) x won't change
        x = Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)
        If x <> 0 Then
            SegmentSearch = Cells(i, 3)
            Exit For ' Done
        End If
    Next i
End Function

HTH,
~ Jim

Sorry, hadn't seen your later posts when I replied. This code you gave me works perfectly, it's amazing!!! Thanks a lot! I think I'll have to study VBA a bit more to understand everything (and especially to reproduce it when needed), but I believe I understand the main mechanisms here.

Once again, thank you very much for your help!

Kind regards,
Pierre

 

[mikerickson]

I'm not seeing Search as an available method for Application; is there a reference that needs to be added for that?

Sorry to hijack your thread Pierre - hopefully you'll find something useful in our discussion...

Cheers,
~ Jim

Its not listed in the Object Browser, but it works. I don't use SEARCH in VBA much, because it duplicates the VBA InStr function.

Application.Match vs. WorksheetFunction.Match (= Application.WorksheetFunction.Match) is where I use it frequently, to avoid a VBA error when the sought term is not present.