How to use VBA code to find and collect

Category: [Excel] Demo Available

How to use VBA code to find and collect

Requirement:

This is a rather complex macro request.

Sample workbook attached.

1. Find cell in column B which is filled with a certain color (there will only be 1 filled cell)
2. If column F in the same row as that cell is not blank, then select the row
3. If column F in the same row as that cell IS blank, then
A. find another row where column A matches AND column F is not blank,
B. select that row

In attached workbook, B13 is colored, but F13 is blank.
So macro should look for another row where column A matches A13 and F is not blank, and select row 14.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1198189-find-select

Solution:

Code:

Sub FindAndSelect()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    Dim sAddr As String
    For Each rng In Range("B10:B" & LastRow)
        If rng.Interior.Color = 16777215 And rng <> "" Then
            If rng.Offset(0, 4) <> "" Then
                rng.EntireRow.Select
            ElseIf rng.Offset(0, 4) = "" Then
                Set foundRng = Range("A:A").Find(rng.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
                If Not foundRng Is Nothing Then
                    sAddr = foundRng.Address
                    Do
                        If foundRng.Offset(0, 5) <> "" Then
                            foundRng.EntireRow.Select
                            Exit Do
                        End If
                        Set foundRng = Range("A:A").FindNext(foundRng)
                    Loop While foundRng.Address <> sAddr
                    sAddr = ""
                End If
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.