Excel VBA resize runtime error

[Glitch5618]

Hello everyone, I've been using this forum as a resource for a project at work and it has been extremely helpful thus far. Thank you all.

I've tried my best to find a solution to my problem on my own but have had no luck. I am running excel 2007. I am very new to VBA and coding all together. This project has been a learning experience thus far.

What I am working on is a userform that allows you to filter through data in several excel worksheets using employee name, manger name, line of business, and training class. To do this I have four combo boxes that display a unique list of their respective data. These combo boxes are then used by the user to filter the data. The code section I'm working on uses the values selected in the combo box to find all matches of only one particular combo box field, selects the range and then resizes the columns to include all corresponding data. This is then loaded into an array.

I've tried searching online resources to figure out how to use vlookup or index match, or even using collections or dictionaries, however due to the constraints of excel these appear to be very limited when it comes to actively looking for duplicates and return all of those values. That and the fact that this data does NOT have any unique fields (nothing reliable like an ID key as in access; many of the names are repeated many times) the only way I've found to do this is to use the following code.

Sub FindValues(f As UserForm)

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
Dim myArr() As Variant


'Search value
  fnd = f.cboSDS.Value


Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, After:=LastCell)


'Test to see if anything was found
  If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
  Else
    GoTo NothingFound
  End If


Set rng = FoundCell


'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(After:=FoundCell)
    
    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)
    
    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do
      
  Loop


'resize and load into array
    rng.Offset(, -1).Resize(, 9).Select
    rng.Interior.Color = RGB(255, 255, 0)
    myArr = Selection.Value


    With f.listEscalation
        .ColumnCount = 9
        .ColumnWidths = ";;;;;;;;;"
        .List = myArr
    End With
    
  
Exit Sub


'Error Handler
NothingFound:
  MsgBox "No values were found in this worksheet"


End Sub

The code that is throwing an error is in green bold. Now I need to mention a few things. First is that for this module I am trying to find all of the team mangers in the list. So i'm trying to get a list of every agent they have data for. If I change the fnd range to the agent combo box (f.cboAgent.value) this will work. I think it has something to do with the fact that the data is filtered by name for the agents when this code is ran, so when I select a name its all together. However the team mangers (f.cboSDS.value) field is all over the place, the selection is split up. Now when I just run the section of code in question as rng.select it works fine. But I get the following error if I attempt to re-size it to include all the data I need...

"Run-time error '1004':
application-defined or object-defined error"

For this sheet of data, the manger name is column "B" and agent names are column "A", so I'm trying to offset the selection to the left one column and then to the right for the rest of the data. I'm at a complete loss as to how to solve this problem. Any information or help from the members of this forum would be extremely appreciated. I hope I've included all the details needed to help me with this issue, I apologize in advance if I've left anything obvious out. Thank you for your time.

 

[CharlesChuckieCharles]

I think

Set rng = Union(rng, FoundCell)

is the problem because its a list of cells as you say all over the place

Maybe instead of rng.Offset(, -1).Resize(, 9).Select
try

for each MyCell in rng

MyCell.offset(,1).resize(,9)
MyCell.select
...

Next MyCell

 

[Glitch5618]

I tried the code but it doesn't appear to work. If I just use the code as is then the line

MyCell.offset(,1).resize(,9)

throws the following error: "Compile error: Expected:="

I tried adding .select to the end which did seem to solve that issue, however the code now only selects one line not all of the duplicates, so for my purposes this wouldn't work, thank you for taking the time to reply though. Perhaps i'm doing it wrong somehow?

Also if there is a better way to accomplish what i'm trying to do i'm open to any suggestions. This is just the best way I could find. However I hate to have to use .select since my understanding is that its very inefficient.

 

[Glitch5618]

anyone have any further suggestions? Nothing seems to be working