Excel VBA模拟“不在"SQL功能 [英] Excel VBA Simulating "Not In" SQL functionality

问题描述

全部-

我有2张Excel.

表1为三列(名称，日期，值)表格2是名称.

我想编写一个VBA脚本，显示所有工作表1数据，这些数据在工作表1的任何地方都没有工作表2中列出的任何名称字段(名称可以出现在不同的列中，因此理想情况下它将搜索工作表中的所有单元格1)出现在工作表3

中

请参阅示例图像，以粗略了解我希望完成的工作.我已经搜索过但还没有运气.

解决方案

欢迎使用堆栈溢出！

您没有说出源表和标准表在哪里开始，或者反过滤器"的结果在哪里放置.我编写这些代码的前提是它们都始于工作表的第一个单元格A1:

  Sub AntiFilter()昏暗aSource作为范围，aCriteria作为范围，oCell作为范围，oTarget作为范围，countCells作为长设置aSource = Worksheets("Sheet1").Range("A1").CurrentRegioncountCells = aSource.Columns.Count设置aCriteria = Worksheets("Sheet2").Range("A1").CurrentRegion设置oTarget = Worksheets("Sheet3").Range("A1")aSource.AdvancedFilter动作:= xlFilterInPlace，CriteriaRange:= aCriteria，Unique:= False对于Application.Intersect(aSource，aSource.Columns(1))中的每个oCell如果oCell.RowHeight<1然后oCell.Resize(1，countCells).复制目标:= oTarget设置oTarget = oTarget.Offset(1，0)万一下一个oCell关于错误继续aSource.Worksheet.ShowAllData出错时转到0结束子 

All -

I have a 2 sheet excel.

Sheet 1 is three columns (name, date, value) Sheet 2 is name.

I want to write a VBA script that displays all of Sheet 1 data that does NOT have any of the name field listed in Sheet 2 anywhere in sheet 1 (name can appear in different columns so ideally it would search all cells in Sheet 1) to appear in sheet 3

See the sample image for a rough idea of what I"m hoping to accomplish. I have searched but have not had luck.

解决方案

Welcome to Stack Overflow!

You did not say where the source table and criteria table begin, or where to place the result of the "anti-filter". I wrote this code on the assumption that they all start at the first cell of the worksheet, A1:

Sub AntiFilter()
Dim aSource As Range, aCriteria As Range, oCell As Range, oTarget As Range, countCells As Long
    Set aSource = Worksheets("Sheet1").Range("A1").CurrentRegion
    countCells = aSource.Columns.Count
    Set aCriteria = Worksheets("Sheet2").Range("A1").CurrentRegion
    Set oTarget = Worksheets("Sheet3").Range("A1")
    aSource.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=aCriteria, Unique:=False
    For Each oCell In Application.Intersect(aSource, aSource.Columns(1))
        If oCell.RowHeight < 1 Then
            oCell.Resize(1, countCells).Copy Destination:=oTarget
            Set oTarget = oTarget.Offset(1, 0)
        End If
    Next oCell
    On Error Resume Next
    aSource.Worksheet.ShowAllData
    On Error GOTO 0
End Sub

Workbook with macro, test data and examples of selection criteria on Sheet2

If the macro does not work as expected, make sure that you have sheets named Sheet1, Sheet2, and Sheet3 in your workbook, and that the source data range and criteria range start with cells A1. If this is not the case, make the necessary changes to the text of the macro:

这篇关于Excel VBA模拟“不在"SQL功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！