遍历非连续列 [英] Loop through non-continuous columns

问题描述

我有一个工作表(MWS)，第1至5列有名称列表.我想如果在第2、3、4、5列中的任何一个中也找到了第1列的名称，那么该名称将放在第6列中.这就是我想出的在第1列中完成任务的地方.

I have a worksheet (MWS), with column 1 to 5 having list of names. I would like if names on column 1 were also found in any of column 2, 3, 4, 5, the name would be put onto column 6. Here is what I come up with to do the task on column 1.

Sub testdup()

Dim MWS As Worksheet, LR As Long, i As Long
Set MWS = ThisWorkbook.Worksheets("Scope")
For i = 2 To MWS.UsedRange.Rows.Count
LR = Application.WorksheetFunction.CountA(MWS.Range("F:F"))
If Not MWS.Columns(2).Find(what:=MWS.Cells(i, 1)) Is Nothing Then
MWS.Cells(LR + 1, 6).Value = MWS.Cells(i, 1).Value
End If 
Next i

For i = 2 To MWS.UsedRange.Rows.Count
LR = Application.WorksheetFunction.CountA(MWS.Range("F:F"))
If Not MWS.Columns(3).Find(what:=MWS.Cells(i, 1)) Is Nothing Then
MWS.Cells(LR + 1, 6).Value = MWS.Cells(i, 1).Value
End If 
Next i

'and repeat the for loop for column 4, 5

现在，由于我必须对第2列中的第1、3、4、5列名称和第3列中的第1、2、4、5列名称进行循环，依此类推，这涉及非-连续的列号.我应该添加什么代码来使MWS.Column(x)可变?谢谢.

Now, since I have to do the loop for names on column 2 with column 1, 3, 4, 5 and for names on column 3 with column 1, 2, 4, 5, so on so forth, which involves non-continuous column numbers. What codes should I add to make MWS.Column(x) variable? Thank you.

推荐答案

使用 COUNTIF 尝试此方法.我还没有测试过，所以让我知道你的情况.

Try this method using COUNTIF. I haven't tested it so let me know how you get on.

这不会检查该值是否已经放置在F列中.如果您希望这样做，可以在添加该值之前对F列进行另一个简单的COUNTIF检查.

This doesn't check if the value has already been placed in column F. If you wanted this you could do another simple COUNTIF check on column F before adding the value.

Sub testdup()

Dim MWS As Worksheet, r As Long, rData As Range, c As Long

Set MWS = ThisWorkbook.Worksheets("Scope")
Set rData = MWS.Range("A2", MWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 5)

With WorksheetFunction
    For c = 1 To 5
        For r = 1 To rData.Rows.Count
            If .CountIf(rData, rData.Cells(r, c)) - .CountIf(rData.Columns(c), rData.Cells(r, c)) > 0 Then
                MWS.Range("F" & Rows.Count).End(xlUp)(2).Value = rData.Cells(r, c).Value
            End If
        Next r
    Next c
End With

End Sub

这篇关于遍历非连续列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！