+ Reply to Thread
Results 1 to 5 of 5

Modifying Existing Code + Find Range Differences

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    116

    Modifying Existing Code + Find Range Differences

    I have a bit of code that checks the values in 2 ranges for differences, and then prints (in a cell range) the differences between the 2 ranges.
    I would like to modify this a little, instead of having the columns hard coded in the module, I would like the user to be able to select the ranges they would like to compare.

    Here is what I have for code:

    Sub Main()
    
    Application.ScreenUpdating = False
    
    Dim stNow As Date
    stNow = Now
    
    Dim varr As Variant
    varr = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
    Dim arr As Variant
    arr = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row).Value
    
    Dim x, y, match As Boolean
    For Each y In arr
        match = False
        For Each x In varr
            If y = x Then match = True
        Next x
        If Not match Then
    
            Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = y
    
        End If
    Next
    Range("B1") = "Items not in A Lists"
    Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 2) = "Items not in I Lists"
    
    'Dim arr As Variant
    arr = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
    'Dim varr As Variant
    varr = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row).Value
    
    'Dim x, y, match As Boolean
    For Each x In arr
        match = False
        For Each y In varr
            If x = y Then match = True
        Next y
        If Not match Then
            Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = x
        End If
    Next
    
    
    Debug.Print DateDiff("s", stNow, Now)
    Application.ScreenUpdating = True
    
    End Sub
    I found this code here:
    HTML Code: 
    Posted by "crazynut"

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Modifying Existing Code + Find Range Differences

    Hi

    I think that the easiest way to do this is to select one range before running the Macro.

    Then use a input box to enter the address of the First Cell in the second Range.

    What do you think?
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    116

    Re: Modifying Existing Code + Find Range Differences

    I suppose I was thinking that:

    Set Range1 = Application.Selection
        Set Range1 = Application.InputBox("Select Range 1 :", xTitleId, Range1.Address, Type:=8)
        Set Range2 = Application.InputBox("Select Range 2:", xTitleId, Type:=8)
    Was the route I was going to go down. But wasn't sure if that was the best course of action?

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Modifying Existing Code + Find Range Differences

    
    Sub Main()
    
    Application.ScreenUpdating = False
    
    Dim stNow As Date
    stNow = Now
    
    Dim varr As Variant
    Set varr = Selection
    MRows = Selection.Rows.Count
    
    If varr.Columns.Count > 1 Then Exit Sub
    
    On Error Resume Next
    
    
    Dim arr As Variant
    T = InputBox("Select start of Second Range", vbOKOnly)
    Set arr = Range(T)
    With arr
    r = .Row
    c = .Column
    End With
    
    If r = "" Or c = "" Then Exit Sub
    
    Set arr = Cells(r, c).Resize(MRows, 1)
    
    Dim x, y, match As Boolean
    
    For Each y In arr
        match = False
        For Each x In varr
            If y = x Then match = True
        Next x
        If Not match Then
    
            Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = y
    
        End If
    Next
    Range("B1") = "Items not in A Lists"
    Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 2) = "Items not in I Lists"
    
    'Dim arr As Variant
    arr = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
    'Dim varr As Variant
    varr = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row).Value
    
    'Dim x, y, match As Boolean
    For Each x In arr
        match = False
        For Each y In varr
            If x = y Then match = True
        Next y
        If Not match Then
            Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = x
        End If
    Next
    
    
    Debug.Print DateDiff("s", stNow, Now)
    Application.ScreenUpdating = True
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    116

    Re: Modifying Existing Code + Find Range Differences

    Couple of questions:

    The input box does not allow me to select the starting point of the second range, I can however type in the start point.
    Also, would't this part of the code need to be updated to reflect the users selection?

    'Dim arr As Variant
    arr = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
    'Dim varr As Variant
    varr = Range("I3:I" & Range("I" & Rows.Count).End(xlUp).Row).Value
    
    'Dim x, y, match As Boolean
    For Each x In arr
        match = False
        For Each y In varr
            If x = y Then match = True
        Next y
        If Not match Then
            Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1) = x
        End If
    Next

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Modifying existing code to save worksheet
    By Woopwoop in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-26-2015, 08:54 PM
  2. Inserting new columns and modifying existing VBA related code
    By kriminaal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2015, 05:23 PM
  3. Modifying existing code
    By rhouston08 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2015, 06:03 PM
  4. [SOLVED] Add two more columns, modifying an existing VBA code help.
    By Anka in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 02-17-2013, 01:24 PM
  5. Modifying Existing Code to Check if the entry exists
    By jason_kelly in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-31-2010, 10:39 AM
  6. Modifying existing VBA code to find different file name
    By PittsburghEng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2010, 03:24 PM
  7. Help modifying existing VBA code
    By camcafe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2008, 11:02 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1