 |
|
|
|
|
|
There are many ways to Average a range of numbers conditionally in
Excel
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