Help in finding errors

[harishankarin]

Hi,
I am trying to automate a process using VBA. Please help to find errors in my logic.

I have a Sheet1 that contains the descriptions of over 10,000 different vehicles. I have to Search those descriptions in sheet 1 and map to the corresponding vehicle… I have another sheet with the list of vehicles.
I have column A, B, C and D in a lookup sheet2.
Column A has the list of all vehicle types like car,bus, train etc which should be mapped to a description in sheet1.
Column B and C – has Search keywords that should be found in the description
Column D – has Search keywords that should not be found in the description

Column B is mandatory. Whereas Column C and D are optional and they can hold additional information to help select the appropriate vehicle name from the Column A when I search the descriptions.

It is working and I think it works fine, but I am not sure if it is 100% correct. I am not sure if this is the best way to do it.
Help me to possibly find any errors in my code.



ColA SearchWord1(found) SearchWord2(found) Search Word 3(not found)
Ferrari Ferrari car horse
Mclaren Mclaren car engine
train train
bus Bus public transport


So, when I search in a description, if I see Ferrari and car and it does not have horse in it, then I will identify it as "Ferrari"
-when I search in a description, if I see Mclaren and car and it does not have engine in it, then I will identify it as "Mclaren"
-when I search in a description, if I it has train, then I will identify it as "train"
-when I search in a description, if I see bus and public transport, then I will identify it as "bus"

So, the first keyword is mandatory.. 2nd and 3rd keyword are optional. 2nd keyword should be found in the desc and 3rd keyword should not be found in the desc



Here is my code.

   Sub A()

   start1 = 4
    start2 = 3
    
    Set descr = Range("C" + CStr(start1) + ":C999999")
    rowNum = start1
   Set srchs = Worksheets("Key").Range("B" + CStr(start2) + ":B999999")
 
    For Each asn In descr
        txt = asn.Value
        
        If txt <> "" Then
            Set itk = Range("D" + CStr(rowNum))
            Set Match = Range("G" + CStr(rowNum))
       
            srchRowNum = start2
          '   Set E = Range("E" + CStr(rowNum))     Set F = Range("F" + CStr(rowNum))
            
            For Each srch In srchs
                If srch.Value <> "" Then
                    If InStr(LCase(txt), LCase(srch.Value)) > 0 Then
                        srchsB = ""
                        srchsC = ""
                        srchsB = Worksheets("Key").Range("C" + CStr(srchRowNum)).Value
                        srchsC = Worksheets("Key").Range("D" + CStr(srchRowNum)).Value

                        
                       If srchsB <> "" And srchsC <> "" Then
                           If InStr(LCase(txt), LCase(srchsB)) > 0 Then
                                If InStr(LCase(txt), LCase(srchsC)) > 0 Then
                                    i = 1
                                Else
                                    itk.Value = Worksheets("Key").Range("A" + CStr(srchRowNum)).Value
                                  '  E.Value = 1           '  F.Value = 2
                                End If
                           End If
                        
                        ElseIf srchsB <> "" And srchsC = "" Then
                           If InStr(LCase(txt), LCase(srchsB)) > 0 Then
                             itk.Value = Worksheets("Key").Range("A" + CStr(srchRowNum)).Value
                                   ' E.Value = 3            '  F.Value = 4
                           End If
                      
                        ElseIf srchsB = "" And srchsC <> "" Then
                           If InStr(LCase(txt), LCase(srchsC)) > 0 Then
                             i = 1
                           Else
                             itk.Value = Worksheets("Key").Range("A" + CStr(srchRowNum)).Value
                            ' E.Value = 5    ' F.Value = 6
                           End If
                        
                        ElseIf srchsB = "" And srchsC = "" Then
                          itk.Value = Worksheets("Key").Range("A" + CStr(srchRowNum)).Value
                                 '   E.Value = 7 '   F.Value = 8
                        End If

                    End If
                    
                 srchRowNum = srchRowNum + 1
                Else
                    Exit For
                End If
            
            Next
        Else
            Exit For
        End If
        
        rowNum = rowNum + 1
        
  Next
         
End Sub