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:
- 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)
- The same workbook has the code module, which has the Reconcile list - existing values to compare the new values to (wb)
- The workbook with the Watch list - the new values to be compared - the location being specified in the Active Workbook above (wbDirty)
- 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.
- 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.
- 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.
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
Related videos on Youtube
Author by
phillipsk
Updated on September 18, 2022Comments
-
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 inrngreconcile
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 about 9 yearsNice 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 about 9 yearsA 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 about 9 yearsYou're just trying to shake out uniques?
-
phillipsk about 9 yearsYes, I am trying to shake out uniques via this VBA procedure
-
Brian Folan about 9 yearsgrrr.... didn't see the "ActiveWindow.Close Savechanges:=False" at the end, and the last 10min of scripting got wiped...
-
Raystafarian about 9 yearsSo what's the problem? What happens? What should happen? Any errors? Where? Why can't you use proven unique identification methods?
-
-
Brian Folan about 9 yearsThey 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 about 9 yearsUm, 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 about 9 yearsOf course, Thanks Glenn. (interesting that it worked when i tested it)
-
phillipsk about 9 yearsIs rngWatch_last_cell a range and not an integer?
-
Brian Folan about 9 yearsNope, 'rngWatch_last_cell = Sheets(1).Range("A65536").End(xlUp).Row' sets it to the row number of the last cell filled in
-
phillipsk about 9 yearsThank 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 atNew_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 theNew_Path
variable showsNew_Path = ""
-
GlennFromIowa about 9 yearsSo 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 about 9 yearsI 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 suchyrow = 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 theR1C1
formula? -
GlennFromIowa about 9 yearsAh, 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
ORSet 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 A1048576Range("A1048576").End(xlUp)
is A125 (filled)Range("A125")(2)
is A126 (blank)