Search code examples
excelvba

Exact match with InStr

I have a spreadsheet with product descriptions. In the description there are various combinations.
Pack of 1, PACK OF 1, 1 PAck etc.

I have written code whereby I can configure a table of pack descriptions and sizes, to populate a field with the numeric pack size.

Table - trying to populate last column with number based on the pack found in the title
Table - trying to populate last column with number based on the pack found in the title

Pack Configuration
Pack Configuration

The code runs but it is matching PACK OF 10 in the title with Pack of 1 and putting a 1 in the end column.

I tried various combinations of the InStr syntax.

Is there a way to search for an exact match in the string?

The code works apart from this piece.

'Reset filter and 'Process PackSize not updated in filter process
PackListWs.Activate
ApdTbl.AutoFilter.ShowAllData
PLCount = PackTbl.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
SizeCount = SizeTbl.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
AmzDataWs.Activate
For i = 6 To ApdCount
   
  If Sheets("DATA - Product Validation").Range("i" & i).Value = "" Then
    If Sheets("DATA - Product Validation").Range("g" & i).Value <> "" Then
      k = 2
      For k = 2 To PLCount
        sc = 2

        For sc = 2 To SizeCount
          PackSize = PackListWs.Range("e" & sc).Value
          PackFilter = PackListWs.Range("c" & k).Value
    
          PackFilter = Replace((PackFilter), "*", PackSize)
          PackFilter = "*" & PackFilter & "*"
            
          Application.StatusBar = "Processing Amazon Pack Sizes without brackets -  " & PackFilter
          StrToChk = AmzDataWs.Range("g" & i).Value

          If InStr(1, (StrToChk), (PackFilter), vbTextCompare) > 0 Then
            AmzDataWs.Range("i" & i) = PackSize
          Else
            GoTo NoUpdate2
          End If

NoUpdate2:
        Next sc
      Next k
    End If
  End If
  
Next i
Solution

  • Here's something to try:

    Sub Doit()

    Dim sTest As String
    sTest = "qwerwqerqwerweqrqwer (Pack of 10)"
    
    
    Dim iLocate_PackOf As Long
    Dim sPackOf As String
    Dim aPackOf() As String
    Dim sCount As String
    Dim iCount As Long
    
    sTest = LCase(sTest) ' Set to lowercase
    sTest = Trim(Replace(sTest, ")", " ")) ' replace any right penrens with space, then trim
    iLocate_PackOf = InStr(1, sTest, "pack of ")
    
    iCount = 0
    
    If iLocate_PackOf > 0 Then
    
        sPackOf = Right(sTest, Len(sTest) - iLocate_PackOf) ' Extract "pack of XX"
        aPackOf = Split(sPackOf, " ") ' Split "pack of XX" into array
        sCount = aPackOf(UBound(aPackOf)) ' Get last element, XX
        
        If IsNumeric(sCount) Then ' If it's a number then get count.
            iCount = CLng(sCount)
        End If
    End If

End Sub