Use Excel VBA If value in column a is not in column b then add value to column c

8,060

Based on your comment that ideally you'd like two workbooks here is an updated description and code:

  1. The Active Workbook, which has a Named range with the path of another workbook with the Watch list (wb) Note: You could use either Client_watchlist (ActiveWorkbook) or wb (ThisWorkbook)
  2. The same workbook has the code module, which has the Reconcile list - existing values to compare the new values to (wb)
  3. The workbook with the Watch list - the new values to be compared - the location being specified in the Active Workbook above (wbDirty)
  4. The Active Workbook contains the Watch values that don't exist in the Reconcile list.
    • You no longer need a Named Range in the Active Workbook named ResultPath.
    • Completely took out the compiler variable.
  5. The Active Workbook must be open, and the other workbook must exist , but not be open. You could, of course, put error checking in to see if it is already open.
  6. Since you changed the ScreenUpdating property, I put in the matching reset at the bottom (this can cause lots of panic if not reset!). Also put in some status updates. May not be necessary if it runs quickly enough.
  7. Finally, I used the Application.WorksheetFunction.Match function to scan for the existing values in the "Reconcile" list. In my tests, that ran about 7 times faster than cycling through the list, checking for each value individually, but that may vary depending on the type of data and how many values there are.

    Option Explicit
    
    '#Const NewRangeInActiveWorkbook = False
    
    Sub Client_Dirty_Recon()
    
    Dim nRow As Long                        ' Last filled cell in column
    Dim c As Range                          ' rngWatch.Cells(i, 1).Value
    Dim oldStatusBar As Variant             ' Save StatusBar status
    'Dim Client_watchlist As Workbook        ' ActiveWorkbook
    Dim Client_path As String               ' Range("Path")
    'Dim New_path As String                  ' Range("ResultPath")
    Dim wb As Workbook                      ' ThisWorkbook
    Dim wbDirty As Workbook                 ' Workbooks.Open(Client_path)
    'Dim wbNew As Workbook                   ' Result of compare
    Dim rngReconcile As Range               ' wb.Sheets(1).Range("K:K")
    Dim rngWatch As Range                   ' wbDirty.Sheets(1).Range("A:A")
    Dim rngNew As Range                     ' wbNew.Sheets(1).Range("A:A")
    
    oldStatusBar = Application.DisplayStatusBar     'optional - save StatusBar
    Application.DisplayStatusBar = True             'optional - turn on StatusBar
    Application.ScreenUpdating = False              'optional - screen won't flash
    Application.StatusBar = "Opening workbooks..."  'optional - Update user
    
    'Set Client_watchlist = ActiveWorkbook
    'Client_path = Client_watchlist.Names("Path").RefersToRange.Value
    
    Set wb = ThisWorkbook
    Client_path = wb.Names("Path").RefersToRange.Value
    ' Get only used part of column
    Set rngReconcile = wb.Sheets(1).Range("K:K")
    nRow = rngReconcile(rngReconcile.Cells.Count).End(xlUp).Row   ' Get last filled cell
    Set rngReconcile = Range(rngReconcile(1), rngReconcile(nRow)) ' Reduce rng size
    
    Set wbDirty = Workbooks.Open(Client_path)   ' Assumes it exists and is not open
    ' Get only used part of column
    Set rngWatch = wbDirty.Sheets(1).Range("A:A")
    nRow = rngWatch(rngWatch.Cells.Count).End(xlUp).Row     ' Get last filled cell
    Set rngWatch = Range(rngWatch(1), rngWatch(nRow))       ' Reduce range size
    
    '#If NewRangeInActiveWorkbook Then
    '    Set wbNew = Client_watchlist  ' Change #Const above to assign to existing wb
    '#Else
    '    New_path = Client_watchlist.Names("ResultPath").RefersToRange.Value
    '    Set wbNew = Workbooks.Open(New_path) ' Assumes it exists and is not open
    '#End If
    'Set rngNew = wbNew.Sheets(1).Range("A1") ' Starts in A1; or could put title here
    Set rngNew = wb.Sheets(1).Range("A1") ' Starts in A1; or could put title here
    
    For Each c In rngWatch                   ' Each value in rngWatch
        On Error Resume Next                 ' Interrupt Error checking
        If IsError(WorksheetFunction.Match( _
            c.Value, rngReconcile, 0)) Then  ' If not in rngReconcile
            rngNew.FormulaR1C1 = c.Value     ' Copy to rngNew
            Set rngNew = rngNew(2)           ' Moves range down =Offset(rngNew,1,0)
        End If
        On Error GoTo 0                      ' Reset Error checking
        If (c.Row + 1) Mod 100 = 0 Then      'optional - Update user
            Application.StatusBar = "Evaluating cell " & c(2).Address & "..."
        End If
    Next c
    
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar ' Reset Status Bar
    Application.ScreenUpdating = True           ' Very important: turn this back on!
    
    ActiveWindow.Close Savechanges:=False       ' Not sure why this was here, but...
    
    End Sub
    
Share:
8,060

Related videos on Youtube

phillipsk
Author by

phillipsk

Updated on September 18, 2022

Comments

  • phillipsk
    phillipsk about 1 year
    Dim i As Variant
        'For i = 1 To 5
         For Each i In rngWatch
         Dim c As Variant
         c = rngWatch.Cells(i, 1).Value
         If i <> rngReconcile.Cells(i, 1).Value Then
    
    
            MsgBox i
    
            End If
    
            Next i    
    

    The above for loop and if statement is where I am lost. If a value from rngwatch is not in rngreconcile then I would like to add the value to a new range (yet to be defined)

    Sub Client_Dirty_Recon()
    
    Dim Client_path As String
    Dim Client_watchlist As Workbook
    Dim Client_client_email As Workbook
    Set Client_watchlist = ActiveWorkbook
    Dim email_range As Range
    Dim watchlist_range As Range
    Dim wb As Workbook
    Dim wbDirty As Workbook
    
    Set wb = ThisWorkbook
    Application.ScreenUpdating = False  'optional - screen will not flash
    
    Client_path = Range("Path")
    Workbooks.Open Client_path
    Dim recon_list As Range
    Set wbDirty = Workbooks.Open(Client_path)
    Dim rngReconcile As Range
    Dim rngWatch As Range
    Set rngReconcile = wb.Sheets(1).Range("K:K")
    Set rngWatch = wbDirty.Sheets(1).Range("A:A")
    
    
    
    Dim i As Variant
        'For i = 1 To 5
         For Each i In rngWatch
         Dim c As Variant
         c = rngWatch.Cells(i, 1).Value
         If i <> rngReconcile.Cells(i, 1).Value Then
    
    
            MsgBox i
    
            End If
    
            Next i
    
    ActiveWindow.Close Savechanges:=False
    
    End Sub
    
    • Prasanna
      Prasanna about 9 years
      Nice question. What exactly do you want? Is your VBA not working or do you want to do it without VBA? To quote your question "If value in column a is not in column b then add value to column c" Which value should be added to col. C??
    • phillipsk
      phillipsk about 9 years
      A for loop and or if statement? that which scans through rngWatch to see if a value in this range is NOT if rngReconcile, if the value is in rngWatch is NOT in rngReconcile then add that unique value to a new range (i.e. column c of wb)
    • Raystafarian
      Raystafarian about 9 years
      You're just trying to shake out uniques?
    • phillipsk
      phillipsk about 9 years
      Yes, I am trying to shake out uniques via this VBA procedure
    • Brian Folan
      Brian Folan about 9 years
      grrr.... didn't see the "ActiveWindow.Close Savechanges:=False" at the end, and the last 10min of scripting got wiped...
    • Raystafarian
      Raystafarian about 9 years
      So what's the problem? What happens? What should happen? Any errors? Where? Why can't you use proven unique identification methods?
  • Brian Folan
    Brian Folan about 9 years
    They are both integers, don't see how. Debug the error, and put your mouse over rngWatch_var and rngWatch_last_cell and see what they say
  • GlennFromIowa
    GlennFromIowa about 9 years
    Um, actually, rngWatch_var is a Variant. The syntax for declaring multiple integers on the same line would be this: Dim rngWatch_var As Integer, rngReconcile_var As Integer Dim Statement
  • Brian Folan
    Brian Folan about 9 years
    Of course, Thanks Glenn. (interesting that it worked when i tested it)
  • phillipsk
    phillipsk about 9 years
    Is rngWatch_last_cell a range and not an integer?
  • Brian Folan
    Brian Folan about 9 years
    Nope, 'rngWatch_last_cell = Sheets(1).Range("A65536").End(xlUp).Row' sets it to the row number of the last cell filled in
  • phillipsk
    phillipsk about 9 years
    Thank you, your answer is very helpful. I would like to clarify further. There are only 2 workbooks in my ideal scenario. The activeworkbook, which the code is stored in, is open at all times. We would then run the macro through the activeworkbook which would open wbDirty and resume your code. The code currently stops at New_path = Client_watchlist.Names("ResultPath").RefersToRange.Value -- I added the 'ResultPath' named range to the activeworkbook, now I receive a type mismatch error. Hovering over the New_Path variable shows New_Path = ""
  • GlennFromIowa
    GlennFromIowa about 9 years
    So in that case, you shouldn't even need the 'ResultPath' named range. I made some changes to the code above (commenting out obsolete portions to provide some continuity), but this should work for the 2 workbook scenario you described.
  • phillipsk
    phillipsk about 9 years
    I added another variable following your convention. I would like the unique value found through the for loop to be added to the end of rngNew as so I defined another long variable and incorporated as such yrow = rngNew(rngNew.Cells.Count).End(xlUp).Row Set rngNew = Range(rngNew(1), rngNew(yrow)) But now I believe I need to incorporate this range into the the for loop, preferably altering the R1C1 formula?
  • GlennFromIowa
    GlennFromIowa about 9 years
    Ah, you want to add to col A as new data comes in. rngNew begins as a single cell, so you can keep For loop the same. Just before For loop, do either this: yrow = wb.Sheets(1).Range("A:A")(rngNew.Cells.Count).End(xlUp).Row Set rngNew = wb.Sheets(1).Range("A:A")(yrow+1) 'Add DIM OR Set rngNew = wb.Sheets(1).Range("A:A")(rngNew.Cells.Count).End(xlUp)(2) The parens in Range(x) always stack down the column for a single cell range. If last filled cell=A125: Range("A:A")(rngNew.Cells.Count) is A1048576 Range("A1048576").End(xlUp) is A125 (filled) Range("A125")(2) is A126 (blank)