Results 1 to 7 of 7

VBA - Compare cells in a range with a loop?

This is a discussion on VBA - Compare cells in a range with a loop? within the Excel Questions forums, part of the Question Forums category; Hi, Hope you can help. I'm trying to compare the text in two ranges of cells. eg. Sheet1 A1:B4 compare ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    London, England
    Posts
    1

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384

    Default

    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.

    Code:
    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

    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Östersund, Sweden
    Posts
    1,922

    Default

    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


    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Yes, exactly what I'm saying...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    New Member
    Join Date
    Mar 2011
    Posts
    1

    Default Re: VBA - Compare cells in a range with a loop?

    Quote Originally Posted by Juan Pablo González View Post
    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?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com