VBA - Compare cells in a range with a loop?

[Pantsman]

Hi,

Hope you can help. I'm trying to compare the text in two ranges of cells.

eg.
Sheet1 A1:B4 compare with Sheet2 A1:B4
compare A1 with A1, A2 with A2 etc

here is my code to date:

Sub Select_Schedules()

Dim my_Original_Range As Range

Set my_Original_Range = Application.InputBox( _
prompt:="Select the ORIGINAL range of cells", _
Title:="Title here", Type:=8)


Dim my_New_Range As Range

Set my_New_Range = Application.InputBox( _
prompt:="Select the NEW new range of cells", _
Title:="Title here", Type:=8)



For Each c In my_Original_Range.Cells

'This is where i'm struggling!
If c.Value = my_New_Range Then
MsgBox "all ok"
Else
MsgBox "errors"
End If
Next

End Sub


I would really appreciate your assistance.


Regards,


Pantsman

 

[Juan Pablo González]

You could do that with a formula

=IF(SUMPRODUCT((Sheet1!A1:A4<>Sheet2!A1:A4)+0)>0,"Errors","All ok")

You could use the same concept (Using Evaluate) in a macro to get the SUMPRODUCT value.

 

[s-o-s]

On 2002-08-28 04:55, Pantsman wrote:
Hi,

Hope you can help. I'm trying to compare the text in two ranges of cells.

eg.
Sheet1 A1:B4 compare with Sheet2 A1:B4
compare A1 with A1, A2 with A2 etc

here is my code to date:

Sub Select_Schedules()

Dim my_Original_Range As Range

Set my_Original_Range = Application.InputBox( _
prompt:="Select the ORIGINAL range of cells", _
Title:="Title here", Type:=8)


Dim my_New_Range As Range

Set my_New_Range = Application.InputBox( _
prompt:="Select the NEW new range of cells", _
Title:="Title here", Type:=8)



For Each c In my_Original_Range.Cells

'This is where i'm struggling!
If c.Value = my_New_Range Then
MsgBox "all ok"
Else
MsgBox "errors"
End If
Next

End Sub


I would really appreciate your assistance.


Regards,


Pantsman

Hi Pantsman (Nice Name)...

Instead of using a macro with message boxes you can do this using a UDF then selecting the ranges in excel as you would with a normal function. I also think this gives you more flexibility than message boxes.

The function I created is

=CompareStrings(Range1, Range2)

where range1&2 are the ranges to compare.

The code is as follows entered into a module.

Hope this helps
Sean.

Function CompareStrings(Range1, Range2)
Rg1 = Range1.Address
Rg2 = Range2.Address
r1 = 0
CompareStrings = "All OK"
For Each c1 In Range(Rg1).Cells
    r1 = r1 + 1
    r2 = 0
    For Each c2 In Range(Rg2).Cells
        r2 = r2 + 1
        If r1 = r2 Then
            If c1.Value <> c2.Value Then
                CompareStrings = "Errors"
                Exit For
            End If
        End If
    Next c2
    If CompareStrings = "Errors" Then Exit For
Next c1

End Function

 

[Juan Pablo González]

Sean, altough your code works, my "policy" is to never try to mimic something that Excel already does by himself ! you could avoid the loop by using Excel's formulas, and then, for example, use the EVALUATE method to calculate the formula, just like you would in a Spreadsheet.

 

[XL-Dennis]

Hi,

Sorry for stepping in but

my "policy" is to never try to mimic something that Excel already does by himself !

Just a sidenote:
The built-in functions is part of the excel.exe file which means they are "compiled".

Userdefined functions is "interpreated" by Excel since it cannot compile VBA-code.

Conclusion: By using built-in functions we
- don´t need to "invent the wheel again", i e we save development time and
- the code runs faster and more reliable

Kind regards,
Dennis

 

[Juan Pablo González]

Yes, exactly what I'm saying...

 

[Rufio]

You could do that with a formula

=IF(SUMPRODUCT((Sheet1!A1:A4<>Sheet2!A1:A4)+0)>0,"Errors","All ok")

You could use the same concept (Using Evaluate) in a macro to get the SUMPRODUCT value.

This solution looks fine or even more than fine But what if I want to compare two ranges containing numbers and empty cells? Then if I compare 0 with "" (empty cell) your comparsion tells that it is the same value, but in my case it's not.
E.g.:
Sheet1!A1:A4 contains: 4;3;2;1;0
Sheet2!A1:A4 contains: 4;3;2;1;""
formula result is "All ok" but it's not

Any clue how to solve it?