Is there a better way to iterate through two different ranges?

user_18667131 0
Viewed 44

Noob here. I am trying to do a ton in one line and it is shutting down excel every time I try to run it. What I need to do is see if "cell" is in the range on "Scan data". If it is I wanna add 1 to its count.

When I write it like this I get a mismatch error.

    For Each cell In Range("K3:K500")
    i = cell.Value
    For Each cell_n In Workbooks("North Dock Scan Data").Sheets("Scan Data").Range("E3:E500")
        If Range("E" & cell.Row).Value = cell_n.Value Then
            i = (Range("K" & cell_n.Row))
            i = i + 1
            cell.Value = i
        End If
        Next
Next

When I write it like this it shuts down. I need the similar commands but can't debug it any further.

    For Each cell In Range("K3:K500")
    i = cell.Value
    For Each cell_n In Workbooks("North Dock Scan Data").Sheets("Scan Data").Range("E3:E500")
        If str(Range("E" & cell.Row).Value) = str(cell_n.Value) Then
            i = (Range("K" & cell_n.Row))
            i = i + 1
            cell.Value = i
        End If
        Next
Next

I appreciate any help in advance.

vba excel
1 Answers

Getting Rid of the Inner Loop (Application.CountIf)

Dim drg As Range: Set drg = ActiveSheet.Range("E3:E500")
Dim srg As Range: Set srg = Workbooks("North Dock Scan Data") _
    .Worksheets("Scan Data").Range("E3:E500")

Dim Cell As Range

For Each Cell In drg.Cells
    Cell.EntireRow.Columns("K").Value = Application.CountIf(srg, Cell.Value)
Next Cell
user_98140690
Related