Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

Ozgrid Excel Newsletter. Excel Newsletter Archives  

VISIT OUR SPECIALS PAGE | ADVANCED SEARCH |FREE EXCEL & VBA LIFETIME SUPPORT | FREE DEMO DOWNLOADS

Conditional Averages Formulas In Excel. DOWNLOAD DEMO

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.

EXCEL VBA: LOOP THROUGH Column & Find Matches In Other Workbooks

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