I am trying to select identical items in a column, select them and...

[Lidsavr]

Hello everyone,

I have a large list of part numbers. All of the numbers are sorted so like numbers show up together. I have written code to search for a part number. I am having trouble selecting all of the same part numbers plus the two columns to the right.

For instance. My list of numbers might be:

Cell D2: ARCT00232
Cell D3: 880-000171
Cell D4: 880-000191
Cell D5: 880-000195
Cell D6: ARCT00231
Cell D7: AREC00282
Cell D8: AREC00282
Cell D9: AREC00282
Cell D10: AREC00282
Cell D11: AREC00289
Cell D12: AREC00289
Cell D13: AREC00289
Cell D14: AREC00289

In addition, Column E contains the part name and column F contains what we call a designator in our business. Say I want to select just part number AREC00282 and the associated columns to the right in columns E & F.

My code to selects the first occurrence of AREC00282, but I cannot figure the code to select the rest of the same parts and the associated data two columns to the right.

MyVar1 is predefined earlier in the code and is the part number I am searching for.

Here is the code I have so far with the area I am having trouble with in red:

    Dim MyVar1 As String
    Dim MyVar2 As String

    MyVar1 = ActiveCell.Value
    Sheets("ARCT00232").Select
    Range("D:D").Find(What:=MyVar1, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Do
[COLOR="red"]        ActiveCell.Select
        If ActiveCell.Offset(1, 0).Value = ActiveCell Then
            ActiveCell.End(xlDown).Select
        End If
    Loop Until ActiveCell(1, 0).Value <> ActiveCell[/COLOR]    
    MyVar2 = ActiveCell.Offset(0, 1).Value
    Sheets("Bd_PNs").Select
    Range("F1").Select
    Range("F1").Value = MyVar2
    With UserForm4.ListBox1
        .TextAlign = fmTextAlignLeft
        .ControlSource = "'Bd_PNs'!$F$1"
    End With
    If TypeName(Selection) = "Range" Then
        UserForm4.Show
    End If

Any help is appreciated!

Charles

 

[cjcobra]

Can you do an initial COUNTIF on the part number and use that number to loop through your OFFSETs?