Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find All of a Certain Type (Union Object issue)


Ok I'm trying to search for all the rows by the value in Column A, then add Column B of those rows together to find the average.

There are 7 possible ID's in Column A and I need the average value of Column B of each ID.

I have two problems.

1. My first found ID in Column A starts at Row 10, and thats the second time that ID shows up, the first time would be Row 3.

2. As it goes through the Do While loop i get a gobal object error with the 

rngAllMatches = Union(rngAllMatches, rngFound)

This is my code:

For Each ws In Worksheets
    If ws.Name = "RESP Survey" Or ws.Name = "AGT Survey" Or ws.Name = "PRCS Survey" Or ws.Name = "FPU Survey" Or ws.Name = "FPC Survey" Or ws.Name = "FRME Survey" Or ws.Name = "FRMS Survey" Or ws.Name = "HAZM Survey" Or ws.Name = "HAZW Survey" Then
        lastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
        lastCornerCell = "C" & lastRow
        Set myRange = ws.Range("A3:" + lastCornerCell)
        Set rngToSearch = myRange
        i = 1
        For i = 1 To 7

            mySearchValue = "Prompt-" & i
            Set rngFound = rngToSearch.Find( _
                    What:=mySearchValue, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
              MsgBox rngFound.Address
            firstFoundAddress = rngFound.Address

            sum = 0

                rowCoor = rngFound.Row
                sum = sum + ws.Cells(rowCoor, 2).Value

                If rngAllMatches Is Nothing Then
                    Set rngAllMatches = rngFound
                    Set rngAllMatches = Union(rngAllMatches, rngFound)
                End If
                Set rngFound = rngToSearch.FindNext(rngFound)

            Loop While Not rngFound Is Nothing And rngFound.Address <> firstFoundAddress

            count = rngAllMatches.Cells.count
            mean = sum / count

            If i = 1 Then
                ws.Range("G6") = mean
            ElseIf i = 2 Then
                ws.Range("G7") = mean
            ElseIf i = 3 Then
                ws.Range("G8") = mean
            ElseIf i = 4 Then
                ws.Range("G9") = mean
            ElseIf i = 5 Then
                ws.Range("G10") = mean
            ElseIf i = 6 Then
                ws.Range("G11") = mean
            ElseIf i = 7 Then
                ws.Range("G12") = mean
            End If              

        Next i

       End If
Next ws



Hi there, it seems like you might be taking my course but maybe you missed the part where I said that you can't use Union across worksheets - this is probably your issue with that piece of code.  If you are taking my VBA course, the Find tutorial in that course has a fix for it, basically resetting the Union value at the end of the loop for the worksheet, but you could also store all required data into an array or other variable type instead of Union and then parse it at the end of everything.

To start searching for the data in A3, set the range to start at A2 and that should do it.

If you are taking the course and you'd like more personal help, you can use the Contact link at the top of the page and ask me questions directly there.


Answer the Question

You must create an account to use the forum. Create an Account or Login