VBA Excel Find in Range

Posted on 2012-08-17
Medium Priority
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

Assisted Solution

by:Elton Pascua
Elton Pascua earned 600 total points
ID: 38306996
You need to do a loop to find all occurrences.

Below is a basic code taken from http://www.cpearson.com/excel/FindAll.aspx. 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

krishnakrkc earned 900 total points
ID: 38307548

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

569 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