2

This question is slightly different from the existing questions on this site listed here, here & here.

I want to write a UDF in Excel VBA (and not use any existing VBA Functions) to loop thru two ranges simultaneously at same level (1 to 1) and check if the two values match.

enter image description here

Public Function compare(r1 As Range, r2 As Range) As Integer
Dim i
For Each cell In r1
     if cell.Value = 'value from r2 at same range level,  1 to 1 compare
        i = i + 1
     End If
Next cell
compare = i
End Function

In this UDF it takes in two ranges as input. Assume they are single column and equal rows for simplicity. Now using a For Each cell in Range loop I wish to compare the two ranges at same cell level. Is this possible?

Would I need to create a Nested For and in every inner For, skips those many cells to match the cells of Outer For?

Community
  • 1
  • 1
rajeev
  • 137
  • 5

1 Answers1

3

Change to a numbered loop.

Public Function compare(r1 As Range, r2 As Range) As long
    Dim i as long, r as long
    For r=1 to r1.cells.count
         if r1.cells(r).Value = r2.cells(r).Value Then
            i = i + 1
         End If
    Next r
    compare = i    
End Function

There may other benefits from variant arrays, resizing r2 to always be the same rows x columns as r1 and restricting r1 and r2 to the .UsedRange.

Public Function compare(r1 As Range, r2 As Range) As long
    Dim i as long, r as long

    'restrict to .usedrange so full columns can be input
    set r1 = intersect(r1, r1.parent.usedrange)
    'make sure r2 is same dimensions as r1
    set r2 = r2.cells(1).resize(r1.rows.count, r1.columns.count)

    For r=1 to r1.cells.count
         if r1.cells(r).Value = r2.cells(r).Value Then
            i = i + 1
         End If
    Next r
    compare = i    
End Function
rajeev
  • 137
  • 5