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, _
SearchFormat:=False)
MsgBox rngFound.Address
firstFoundAddress = rngFound.Address
sum = 0
Do
rowCoor = rngFound.Row
sum = sum + ws.Cells(rowCoor, 2).Value
If rngAllMatches Is Nothing Then
Set rngAllMatches = rngFound
Else
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