Link to home
Start Free TrialLog in
Avatar of Rick_Preiss
Rick_Preiss

asked on

VBA Excel Find in Range

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

SOLUTION
Avatar of Elton Pascua
Elton Pascua
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial