Использовать Excel VBA Если значение в столбце a отсутствует в столбце b, добавить значение в столбец c

Question

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    

Выше цикл for и оператор if, где я потерян. Если значение из rngwatch отсутствует в rngreconcile то я хотел бы добавить значение в новый диапазон (еще не определен)

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

Answer 1

Исходя из вашего комментария, что в идеале вы хотели бы две книги, здесь обновленное описание и код:

1. Активная рабочая книга с именованным диапазоном с путем к другой рабочей книге со списком наблюдения ( wb). Примечание. Можно использовать либо Client_watchlist (ActiveWorkbook), либо wb (ThisWorkbook).
2. В той же книге есть модуль кода, в котором есть список « Согласование» - существующие значения для сравнения новых значений с ( wb)
3. Рабочая книга со списком наблюдения - новые значения для сравнения - местоположение, указанное в активной рабочей книге выше ( wbDirty)
4. Активная рабочая книга содержит значения Watch, которых нет в списке Reconcile.
   • Вам больше не нужен именованный диапазон в активной рабочей книге с именем ResultPath.
   • Полностью вынул переменную компилятора.
5. Активная рабочая книга должна быть открытой, а другая рабочая книга должна существовать, но не быть открытой. Конечно, вы можете включить проверку ошибок, чтобы увидеть, открыта ли она уже.
6. Так как вы изменили свойство ScreenUpdating, я вставил соответствующий сброс внизу (это может вызвать много паники, если не сбросить!). Также добавьте некоторые обновления статуса. Может не понадобиться, если он работает достаточно быстро.

7. Наконец, я использовал приложение.WorksheetFunction.Функция соответствия для поиска существующих значений в списке "Согласование". В моих тестах это работало примерно в 7 раз быстрее, чем циклически проходить по списку, проверяя каждое значение отдельно, но это может варьироваться в зависимости от типа данных и количества значений.

   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
   

Answer 2

Я пошел в другом направлении ... Проблема, с которой вы столкнулись, заключалась в том, что значение i в вашем коде было значением ячейки, поэтому, когда вы помещаете его в rngReconcile.Клетки (я, 1).Значение вызова, вы вводили текст вместо номера строки.

Следующая проблема, с которой вы столкнулись, заключалась в том, что вы проверяли только каждую строку на строку ... Я считаю, что вам нужно решение для проверки каждого значения в столбце A с каждым значением в столбце K.

Я думаю, что я достаточно вымыл это, чтобы вы могли использовать ... Я уменьшил его, чтобы использовать его только на одном листе, поэтому, если вы этого хотите, я позволю вам вернуть его в несколько рабочих книг ... Надеюсь, вы можете следовать этому.

Dim last_cell As Integer
rngWatch_last_cell = Sheets(1).Range("A65536").End(xlUp).Row
rngReconcile_last_cell = Sheets(1).Range("K65536").End(xlUp).Row


Dim rngWatch_var As Integer, rngReconcile_var As Integer
rngWatch_var = 1
rngReconcile_var = 1

Dim i As Variant
Dim found As Integer

For rngWatch_var = 1 To rngWatch_last_cell
    Dim c As Variant
    c = rngWatch.Cells(rngWatch_var, 1).Value
    found = 0
    For rngReconcile_var = 1 To rngReconcile_last_cell
        If c = rngReconcile.Cells(rngReconcile_var, 1).Value Then
           found = 1
        End If
    Next rngReconcile_var

    If found = 0 Then
        MsgBox c & " not found in list"
        ' add to another column
    End If

Next rngWatch_var