CODEWITHSUNDEEP
vbaexcel
Rachel Shaner
Rachel Shaner

Reputation: 23

Excel formula or VBA code searching column for a list of items and eliminating double counts

I am new to VBA and I couldn't find a built in formula in Excel to return exactly what I'm looking for. I am search a column AN (AN1:AN80627) which is an item description, so it is a sentance. I have a list in column BD (BD1:BD34) of items that I want to see if any of the items are found in the item descriptions.

Some example items are: HDG46a, HDG46, HDG46b

Currently my VBA code is returning in Column C:

HDG46aHDG46

How do I get column C to only return the item that is the longest, ex:

HDG46a

It's finding all matches, but I want it to find the longest character match. My current VBA code is:

Sub FindItems()

    Dim i As Long, j As Long
    Dim ItemName As String
    Dim FirstColumn As Integer

    Range("Bd2:Bd100000").ClearContents

    For i = 1 To 34

        ItemName = Sheets("ItemList").Cells(i + 1, 2).Value

        For j = 1 To 80627

            If InStr(1, Range("AN1").Offset(j, 0).Value, ItemName, vbTextCompare) > 0 Then

                Range("Bd1").Offset(j, 0).Value = Range("Bd1").Offset(j, 0).Value & ItemName

            End If

        Next j

    Next

End Sub

(end of code)

What else to I need to add to the code? Or is there a way to use excel formulas instead of VBA code (that would be great!)?

Thank you and let me know if i need to better explain the issue.

Upvotes: 2

Views: 897

Answers (1)

Sam
Sam

Reputation: 7303

I modified your code a bit and got this.. it seems to work

Function GetLongestValue(ItemName As String, rng As Range)

Dim maxLength As Integer
Dim length As Integer

Dim returnStr As String

maxLength = 0



For Each cell In rng.Value2

    str = Trim(cell)

            If InStr(1, str, ItemName) > 0 Then

            length = Len(str)

                If length > maxLength Then

                maxLength = length
                returnStr = str

                End If

            End If

    Next cell


GetLongestValue = returnStr

End Function

Usage is =getlongestvalue(BD2,$AN$1:$AN$80627) in a worksheet cell...

If you put it in a cell in row 2 and drag down it should give your values...

Upvotes: 1

Related Questions