CODEWITHSUNDEEP
excelvbafindcriteria
Valwrie
Valwrie

Reputation: 133

find row number of cell that contains criteria

I'm needing to find the first row numbers of cell in column C that contains "120" without duplicates (data I have has more than 10 of each number code, I only need the first one). So the code should pick up the first row number containing e.g. 120, 7120, 81200.

The code I've tried below have only managed to find the first row number with cell that contained 120. For reference, AGCL is a column letter derived from another find function and tbAC is a user input into a textbox.

Dim AGCN As Long
Dim AGCL As String
Dim AGNN As Long
Dim AGNL As String
Dim i As Long
Dim RowD As Long
Dim AAC As String
Dim rng As Range
Dim rownumber As Long
Dim AGC As Range
Dim AGN As Range
Dim firstaddress As Long
Dim nextaddress As Long

Set rng = Sheet1.Columns(AGCL & ":" & AGCL).Find(what:="*" & tbAC & "*", 
LookIn:=xlValues, lookat:=xlPart)
    rownumber = rng.Row
    Debug.Print rownumber '9

With Sheet1.Range(AGCL & ":" & AGCL)
    Set c = .Find("*" & tbAC & "*", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Value
        Debug.Print firstaddress
            With Me.ListBox2
           .ColumnCount = 3
           .ColumnWidths = "50;150;70"
           .AddItem
           .List(i, 0) = Str(firstaddress)
           i = o + 1
           End With

        Do
            Set c = .FindNext(c)
            If c Is Nothing Then
                GoTo donefinding
            ElseIf firstaddress <> c.Value Then
                nextaddress = c.Value
                Debug.Print nextaddress 'it doesn't print any value here
                'With Me.ListBox2
                '   .ColumnCount = 3
                '   .ColumnWidths = "50;150;70"
                '   .AddItem
                '   .List(i, 0) = Str(nextaddress)
                '   Debug.Print nextaddress
                '   i = o + 1
                'End With
            End If
        Loop While c.Address <> firstaddress 

    End If
donefinding: Exit Sub
End With

Any help would be greatly appreciated, thank you!

Upvotes: 0

Views: 148

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9907

UPDATED: Okay I updated one last time. As mentioned, I don't know what you want to do with the extra values... but this function will output them where ever...?

good luck.

Here's a custom function that matches what you're looking for, it will return the first time that 120 appears in a cell...

Here's one more that you could use if you truly wanted "contains" only a partial match.

Function SuperSearcherTHING(ivalue As Variant, theColumn As Range) As String
Dim rCell As Range
Const theSPACER As String = "|"
For Each rCell In Intersect(theColumn.EntireColumn, theColumn.Worksheet.UsedRange).Cells
    If InStr(1, rCell.Value, ivalue, vbTextCompare) > 0 Then
        SuperSearcherTHING = rCell.Value & theSPACER & SuperSearcherTHING
    End If

Next rCell

SuperSearcherTHING = Left(SuperSearcherTHING, Len(SuperSearcherTHING) - Len(theSPACER))

End Function

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Here is the Range.FindNext Function you can use to retrieve all the cells having 120. 

With Sheet1.Range(AGCL & ":" & AGCL)
     Set c = .Find("*" & tbAC & "*", lookin:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do

            Set c = .FindNext(c)
        If c is Nothing Then
            GoTo DoneFinding
        Elseif not firstaddress.value = c.value

          ''Whatever you want to do with the Second Found Value
          debug.print c.value

        End If
        Loop While c.Address <> firstAddress
      End If
      DoneFinding:
End With

Now to check that the value already found or not, you can play in the If Condition of this loop. So that you don't get the same values again.

Upvotes: 1

Related Questions