VBA Excel Find in Range

Posted on 2012-08-17
Last Modified: 2012-09-06
I have the following snippet of code that is based off of finding the cell.value from another sheet in a range I have specified, Dest.

However, the code needs to run for all instances of cell.value in range Dest.  As I have it now, it is only finding the first instance and moving to the next cell.value as it is instructed.

How can I modify to find all instances of cell.value in range dest?

Note: I've only posted the relavant script.

    For Each cell In Target
        If Intersect(cell.EntireRow, HMLTarg).Value = "LOW" Then
            Set CellRow = cell.EntireRow
                Debug.Print CellRow.Address
            Set DestRow = dest.Find(cell.Value, LookAt:=xlPart).EntireRow
                Debug.Print cell.Value
                Debug.Print DestRow.Address
                Debug.Print Intersect(DestRow, SumOverall).Value
                Debug.Print Intersect(CellRow, RiskScoreTarg).Value
                    'Populate Sum of Risk Score Column
                    Intersect(DestRow, SumOverall).Value = Intersect(DestRow, SumOverall).Value + Intersect(CellRow, RiskScoreTarg).Value
                    'Populate SLow Column
                    Intersect(DestRow, SumLow).Value = Intersect(DestRow, SumLow).Value + Intersect(CellRow, RiskScoreTarg).Value
                    'Populate Risk Weighted Control Score Column
                    Intersect(DestRow, RiskWtCtrlScoreDest).Value = Intersect(DestRow, RiskWtCtrlScoreDest).Value + Intersect(CellRow, RiskWtCtrlScoreTarg).Value
                    'Increase count + 1
                    Intersect(DestRow, CountCol).Value = Intersect(DestRow, CountCol).Value + 1
        End If

Open in new window

Question by:Rick_Preiss
    LVL 8

    Assisted Solution

    by:Elton Pascua
    You need to do a loop to find all occurrences.

    Below is a basic code taken from That site clearly explains how to do find all occurrences properly.

    Dim FoundCell As Range
    Dim LastCell As Range
    Dim FirstAddr As String
    With Range("A1:A10")
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range("A1:A10").Find(what:="a", after:=LastCell)
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If 
    Do Until FoundCell Is Nothing
        Debug.Print FoundCell.Address
        Set FoundCell = Range("A1:A10").FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If

    Open in new window

    LVL 18

    Accepted Solution


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now