复制具有多个子条件的行 [英] Copy rows with multiple sub criteria

问题描述

我正在处理一个宏，该宏将在列表工作表中搜索不同的县，然后将整行粘贴到当前工作表中.我为每个人都有一个工作表(名为Mark，John等)，每个人都分配了几个县.马克有三个县，列在单元格J1:L1中，我将其命名为范围(MyCounties).我的宏遍历每个县的表"列表"I"列，并将整个行复制到以"A4"开头的标记"表中.我拥有的宏对此非常有效.

I'm working on a macro that will search a List sheet for different counties and then paste the entire row onto the current sheet. I have a worksheet for each person (named Mark, John, etc.) and each person is assigned several counties. Mark has three counties, listed in cells J1:L1, which I've named as a range (MyCounties). My macro looks through Sheet "List" column "I" for each of those counties and copy the entire row onto Sheet "Mark" starting at "A4". The macro that I have works very well for that.

尽管如此，对于洛杉矶县等较大的地区，它分为6个人，每个人在该县内使用不同的城市，在洛杉矶市内使用不同的邮政编码.是否可以搜索匹配的县，然后搜索城市(对于所有非洛杉矶的城市)，然后搜索洛杉矶(城市)，然后搜索邮政编码?县是"I"列，城市是"G"列，邮编是"H"列.因此，安德鲁"将在洛杉矶县的阿罕布拉，阿卡迪亚等城市以及洛杉矶(城市)的邮政编码90004、90006等内.我知道下面发布的宏对此无效，但是否有编辑它的方法以使其达到我想要的效果?我有一个帮助表(洛杉矶")，其中显示A1:D1(合并单元格)中的人名(彼得)，B3和以下是要过滤的县，C3和下是要过滤的城市，D3和下是要过滤的邮政编码.然后我们跳过一列，F1:I1是下一个人.

For larger areas like Los Angeles County though, it gets split up between 6 people, who each take different cities within that county, and within the city of LA itself, take different zip codes. Is it possible to search for matching county and then city (for all cities not LA), as well as for Los Angeles (city) and then zip code? County is column "I", City is column "G", and Zip is column "H". So "Andrew" would have within LA county cities of Alhambra, Arcadia, etc. and also LA (city) zip codes of 90004, 90006, etc. I know that the macro posted below won't work for this, but is there a way to edit it to make it do what I want? I have a helper sheet ("Los Angeles") that shows persons name (Peter) in A1:D1 (Merged Cells), B3 and down are Counties to filter by, C3 and down are cities to filter by, and D3 and down are zip codes to filter by. Then we skip a column, and F1:I1 is next person.

我有一份测试文档，一旦弄清楚该怎么做，便会立即附上.测试文档

I have a test document that I'll attach as soon as I figure out how to do that. test doc

Sub MoreReports()
    Dim w As Long, cVar As Variant, zVar As Variant, rw As Long, sDoc As Worksheet, tDoc As Worksheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set sDoc = Sheets("List")
    Set tDoc = Sheets("Peter")
    Set cVar = Sheets("Los Angeles").Range("C3:C52")
    Set zVar = Sheets("Los Angeles").Range("d3:d52")


         With sDoc
            If .AutoFilterMode Then .AutoFilterMode = False
            With .Range(.Cells(4, "G"), .Cells(.Rows.Count, "I").End(xlUp))
                .AutoFilter field:=3, Criteria1:="Los Angeles", Operator:=xlAnd
                .AutoFilter field:=1, Criteria1:=cVar, Operator:=xlFilterValues
                .AutoFilter field:=1, Criteria1:="Los Angeles", Operator:=xlAnd
                .AutoFilter field:=2, Criteria1:=zVar, Operator:=xlFilterValues
                .AutoFilter field:=3, Criteria1:="Ventura", Operator:=xlFilterValues                    
                With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        .Cells.EntireRow.Copy Destination:=tDoc.Cells(rw, "A")
                    End If
                End With
            End With
            If .AutoFilterMode Then .AutoFilterMode = False
        End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

推荐答案

因此，我将尝试为此提供一个示例(Thomas).请注意，我只查看城市/邮政编码，因为县应该包括这两个唯一值；一些城市可以共享相同的邮政编码.

So, I'm going to try to give one example for this (Thomas). Note that I am looking at the city/zip only, as the county should be inclusive of these two unique values; some cities can share the same zip.

我打算遵循的总体过程是:

The overall process I intend to follow is:

.1)记住参考表上的范围(这样做是为了正确设置匹配项)

.1) Remember the range on the reference sheet (I do this so I can set my matches up correctly)

Sheets("Los Angeles").Range("W4:W15") 'City
Sheets("Los Angeles").Range("X4:X15") 'Zip

.2)记住列表"表上的范围

.2) Remember the range on the List sheet

Sheets("List").Range("G6:G338") 'City
Sheets("List").Range("H6:H338") 'Zip

.3)设置一个循环以对Thomas的引用进行排序

.3) Set-up a loop for sorting through Thomas' references

Dim i As Integer

For i = 15 to 4

.4)在循环中，创建另一个循环以搜索列表中的值

.4) Within the loop, create another loop to search the values on the List

Dim j As Integer

For j = 6 to 338

.5)使用If语句检查条件

.5) Use an If-statement to check the condition

If Match(Sheets("Los Angeles").Range("W"&j),Sheets("List").Range("G"&i))=Match(Sheets("Los Angeles").Range("X"&j),Sheets("List").Range("H"&i)) Then

.6)如果满足条件，我们将剪切并将行粘贴到适当的纸上

.6) If conditions are met, then we will cut and paste the row to the appropriate sheet

Sheets("List").Row(j).Copy Sheets("Thomas").Row(j)

.7)关闭If语句

End If

.8)转到循环中的下一个j

.8) Go to the next j in the loop

Next j

.9)转到循环中的下一个i

.9) Go to the next i in the loop

Next i

.10)按邮政编码过滤托马斯的工作表(应删除所有空行)

.10) Filter Thomas' sheet by Zip code (should remove all empty rows)

Sheets("Thomas").Range("A1:I338").Sort key1:=Range("H1:H338"), order1:=xlAscending, Header:=xlYes

通过这个示例，您应该能够设置对方的工作表！

With this one example, you should be able to set-up each other person's sheets!

总的来说，(看起来可能有更好的方法，但这是我能想到的最简单的Match()方法，在我看来，这比Find()替代方法更容易):

Altogether, it looks like (there may be better ways, but this was the easiest of the Match() things I could think of, which in my head seemed easier than the Find() alternative):

Dim i As Integer
Dim j As Integer

For i = 15 to 4
    For j = 6 to 338

        If Match(Sheets("Los Angeles").Range("W"&j),Sheets("List").Range("G"&i))=Match(Sheets("Los Angeles").Range("X"&j),Sheets("List").Range("H"&i)) Then
            Sheets("List").Row(j).Copy Sheets("Thomas").Row(j)
        End If
    Next j
Next i

这篇关于复制具有多个子条件的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！