There are many ways to Average a range of numbers conditionally in
By far the most efficient way is via a PivotTable. The second most efficient way is via DAVERAGE . The next best of option is SUMIF/COUNTIF and the worse option is via an Array Formula.
Conditional Averages are handy when you wish to exclude certain numbers, most common is to exclude zeros.
This month I want to show a way to loop through a column of data and search all Worksheets of 2 (can be 1+) Workbooks for a match and return a corresponding match. Code is liberal with Comments so you can change to suit
Sub FindMatchesIn2Workbooks() Dim rcell As Range Dim wb1 As Workbook, wb2 As Workbook Dim ws As Worksheet Dim wbRun As Workbook Dim rFind As Range Dim vFind Dim rFound As Range Dim bFound As Boolean 'Workbooks must be open. Change names to suit Set wb1 = Workbooks("Book1.xls") Set wb2 = Workbooks("Book2.xls") 'Code must go in Workbook to locate matches for Set wbRun = ThisWorkbook 'This is the range we loop through and _ try and find matches in 2 other Workbooks 'Change Sheet index and column to suit With wbRun.Sheets(1) Set rFind = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp)) End With 'If no match is found prevent run-time errors On Error Resume Next For Each rcell In rFind 'Loop through cells 'Pass cell value to Variable vFind = rcell 'Set to true IF match found. 'We set back to False each time we search for a new value. bFound = False 'Set our Range variable to Nothing on each cell to find. Set rFound = Nothing 'Loop through all Worksheets in Book1.xls For Each ws In wb1.Worksheets 'Column to search. If ALL cells _ change "ws.Columns(3)" to ws.UsedRange With ws.Columns(3) 'Change to suit Set rFound = .Cells(1, 1) 'Cell to start Find after 'If no match is found rFound is Nothing Set rFound = .Find(What:=vFind, After:=rFound, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rFound Is Nothing Then 'Check if match found bFound = True 'Match Found. Change our Boolean variable to True Exit For 'Exit For Each ws In wb1.Worksheets End If End With Next ws If bFound = False Then 'No Match found in Book1.xls _ Loop through all Worksheets in Book2.xls For Each ws In wb2.Worksheets With ws.Columns(3) Set rFound = .Cells(1, 1) Set rFound = .Find(What:=vFind, After:=rFound, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rFound Is Nothing Then bFound = True Exit For End If End With Next ws End If 'Place the correspoding value (of found match) into _ corresponding cell of match sought after If bFound = False Then 'No Match found rcell(1, 2) = "NOT FOUND" Else 'Match Found. rcell(1, 2) = rFound.Offset(0, -1) End If Next rcell End Sub
See ya next month :)
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft