Search for single item if Multiple items per cell

Question

I'm pretty new to vba, but I do have some working code right now. I execute this code and it clears out cells on one sheet, references an assembly number on that sheet, searches through another sheet for that assembly number, copies the data I want relevant to that assembly number, and pastes in on the original sheet.

This works for the assembly number of interest when there is exactly one assembly number per cell in the spreadsheet database the code looks through. However, if the assembly number doesn't match the cell's exact value (which happens if there are multiple assemblies per cell) then the code passes up that cell and doesn't paste the relevant data.

Is there some way to look within a cell and have the macro recognize whether the assembly number is within an array of assembly numbers within a cell?

Is there a quick way to change the "If Sheets("Stencils").Cells(i, 8).Value = assembly Then" line so that it doesn't need an exact value?

Sub findstencil()
    '1. declare variables
    '2. clear old search results
    '3. find records that match search criteria and paste them

    Dim assembly As String 'Assembly number of interest, containts numbers, letters and dashes
    Dim finalrow As Integer 'determines last row in database
    Dim i As Integer 'row counter

    'clears destination cells
    Sheets("Search").Range("A7:H15").ClearContents

    assembly = Sheets("Search").Range("A5").Value
    finalrow = Sheets("Stencils").Range("C5000").End(xlUp).Row

    For i = 5 To finalrow
        If Sheets("Stencils").Cells(i, 8).Value = assembly Then
            Sheets("Stencils").Cells(i, 3).Resize(1, 6).Copy
            Sheets("Search").Range("B15").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
    Next i

    Sheets("Search").Range("A5").Select
End Sub

When there are multiple numbers in a cell, how is it written? If they're broken up by commas you can use `Split` to create an array, then test against each number. — dwirony, Oct 02 '17 at 15:24
Multiple assemblies in a cell are broken up by different "rows" within each cell. For example, "Assy1" on top, Assy 2 below it in row 2, Assy 3 below that... all within one cell. A space could probably be used as a delimiter then, right? — Mcodewick, Oct 02 '17 at 15:30
If you only want to know that it exists, not where in the cell it is, then INSTR would suffice: If Instr(Sheets("Stencils").Cells(i, 8).Value,assembly ) > 0 then — Harassed Dad, Oct 02 '17 at 15:43
@Mcodewick If they're broken up by one space each time, then yes use that as the delimiter. Or use Harassed Dad's suggestion, to use `Instr` — dwirony, Oct 02 '17 at 15:56
You could use [Like Operator](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator) with wildcards. `If Sheets("Stencils").Cells(i, 8).Value Like "*assembly*" Then` — danieltakeshi, Oct 02 '17 at 16:26
@HarassedDad Thank you!!! This worked really well, INSTR worked just right, that was exactly what I needed. Thank you everyone else as well for your advice! — Mcodewick, Oct 02 '17 at 17:05

u8it · Answer 1 · 2017-10-12T13:15:45.663

Take your pick...

Like Operator

If Cells(i, 3).Value Like "*" & AssemblyNumber & "*" Then

module level statements...

Case-Sensitive

Option Compare Binary

Case-Insensitive

Option Compare Text

InStr

Case-Sensitive

If InStr(1, Cells(i, 3).Value2, AssemblyNumber, 0) > 0 Then

Case-Insensitive

If InStr(1, Cells(i, 3).Value2, AssemblyNumber, 1) > 0 Then

Find method

Set SearchRange = Range(Cells(5, 3), Cells(finalrow, 3))
Set cl = SearchRange.Find( _
    What:=AssemblyNumber, _
    After:=SearchRange.Cells(1, 1), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False)
If Not cl Is Nothing Then
    Sheets("Stencils").Cells(cl.Row, 3).Resize(1, 6).Copy
    Sheets("Search").Range("B15").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Regex

Regex for when it gets real complicated

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Custom Character Analysis

You could even do a character by character comparison if you wanted. I've done this before to implement statistics and find approximate/best-guess matches.

Here's an example the shows how to make a function like InStr that allows for a tolerance in matching...

Function InStrTolerant(InputString As String, MatchString As String, Optional CaseInsensitiveChoice = False, Optional Tolerance As Integer = 0) As Integer
'Similar to InStr, but allows for a tolerance in matching


Dim ApxStr As String 'Approximate String to Construct
Dim j As Integer 'Match string index
j = 1
Dim Strikes As Integer
Dim FoundIdx As Integer

For i = 1 To Len(InputString)

    'We can exit early if a match has been found
    If StringsMatch(ApxStr, MatchString, CaseInsensitiveChoice) Then
        InStrTolerant = FoundIdx
        Exit Function
    End If

    If StringsMatch(Mid(InputString, i, 1), Mid(MatchString, j, 1), CaseInsensitiveChoice) Then
        'This character matches, continue constructing
        ApxStr = ApxStr + Mid(InputString, i, 1)
        j = j + 1
        FoundIdx = i
    Else
        'This character doesn't match
        'Substitute with matching value and continue constructing
        ApxStr = ApxStr + Mid(MatchString, j, 1)
        j = j + 1
        'Since it didn't match, take a strike
        Strikes = Strikes + 1
    End If

    If Strikes > Tolerance Then
        'Strikes exceed tolerance, reset contruction
        ApxStr = ""
        j = 1
        Strikes = 0
        i = i - Tolerance
    End If
Next

If StringsMatch(ApxStr, MatchString, CaseInsensitiveChoice) Then
    InStrTolerant = FoundIdx
Else
    InStrTolerant = 0
End If

End Function

Search for single item if Multiple items per cell

1 Answers1

Linked