Excel宏：迭代行和组合循环 [英] Excel Macro: Iterating through rows and combined loop

问题描述

情况如下：我正在写我的主人论文并做了一个实验，我观察到人的非言语行为。我在一个特定的程序中编码了这个非语言行为，现在作为一个输出我有一个Excel表，其中包含所有的观察数据。事情是，很多行包含我不需要的信息，所以我想删除它们。

我的目标：我只想保留列C和列D的内容的一部分匹配的行（参与者号码，开始从101）。我试图将两个循环组合在一起，所以第一个（内部）循环通过列C和D中的所有参与者号码搜索匹配，一行（直到参与者号码170），如果没有匹配删除行/ if有一个匹配到下一行。 outter循环应该重复包含数据的所有行的inner循环的步骤（这里直到行2732）。

我的代码到目前为止：

  Dim ColumnC As String 
 Dim ColumnD As String 
 Dim ParticipantNumber As String 
 Dim RowNumber As Integer 
 
 Sub SearchAndDeleteRows（）
 RowNumber = 2 
 ParticipantNumber = 101 
 ColumnD =D& RowNumber 
 ColumnC =C& RowNumber 
 
 Do While RowNumber< 2733 
 
尽管参与者号码< 170 
 
如果InStr（Range（ColumnD）.Value，ParticipantNumber）= 0 And InStr（Range（ColumnC）.Value，ParticipantNumber）> 0或InStr（Range（ColumnD）.Value，ParticipantNumber）> 0和InStr（Range（ColumnC）.Value，ParticipantNumber）= 0 Then 
 Rows（RowNumber）.Select 
 Selection.Delete Shift：= xlUp 
 
其他：GoTo NextParticipant 
 
如果
 
 NextParticipant：
 ParticipantNumber = ParticipantNumber + 1 
 
如果ParticipantNumber = 170然后GoTo NextRow 
 End If 
 
循环
 
 NextRow：
 RowNumber = RowNumber + 1 
 
 
循环
 
结束子
  

注意：我知道GoTo功能是邪恶的，但我没有直到现在，都没有办法解决这个问题。

我希望我已经很清楚了。

提前感谢您的帮助！

干杯，J

解决方案

你的逻辑几乎可以，但是.find是一个更快的方式来获得比赛。而且，除非您从下到上开始，否则删除行尝试跟踪行号是不可能的。试试这个。它将创建一个新的工作表，并将所有好的行复制到新的工作表。确保您的活动工作表是正确的，然后再运行它。

  Sub copyNOTdelete（）
 Dim ParticipantNumber As Long， RowNumber As Long 
 Dim wsMain As Worksheet，WSnew As Worksheet，newRowNumber As Long 
 
设置wsMain = ActiveSheet 
设置WSnew = Sheets.Add 
 wsMain.Activate 
 
 RowNumber = 2 
 newRowNumber = 1 
 ParticipantNumber = 101 
 
对于ParticipantNumber = 101到170 
使用wsMain.Range（c2：c2733 ）
设置c = .Find（CStr（ParticipantNumber），LookIn：= xlValues）
如果不是c不是然后
 firstaddress = c.Address 
 Do 
如果wsMain.Range（D& Right（c.Address，1））= ParticipantNumber然后
 WSnew.Rows（newRowNumber）.EntireRow.Value = wsMain.Rows（Right（c.Address，1）） .Value 
 newRowNumber = newRowNumber + 1 
 End If 
 Set c = .FindNext（c）
循环而不是c是没有和c.Address<> firstaddress 
 End If 
 End With 
下一个参与者号
 End Sub 
  

I am struggling to find out where the error in my code is.

The situation is the following: I am writing my master thesis and did an experiment where I observed nonverbal behaviour of people. I coded this nonverbal behaviour in a specific program and now as an output I've got an Excel sheet with all the observational data in it. The thing is, a lot of rows contain information that I don't need, so I want to delete them.

My goal: I want to keep only the rows where part of the content of Column C and Column D match (the participant number, starting from 101). I tried to combine two loops together, so that first (the "inner") loop searches for matches through all participant numbers in Column C and D in one row (until participant number 170), if there is no match deletes the row/ if there is a match goes to the next row. The "outter" loop should repeat the steps of the "inner" loop for all the rows that contain data (here until row 2732).

My code so far:

Dim ColumnC As String   
Dim ColumnD As String
Dim ParticipantNumber As String
Dim RowNumber As Integer

Sub SearchAndDeleteRows()
RowNumber = 2
ParticipantNumber = 101
ColumnD = "D" & RowNumber
ColumnC = "C" & RowNumber

Do While RowNumber < 2733

Do While ParticipantNumber < 170

If InStr(Range(ColumnD).Value, ParticipantNumber) = 0 And InStr(Range(ColumnC).Value, ParticipantNumber) > 0 Or InStr(Range(ColumnD).Value, ParticipantNumber) > 0 And InStr(Range(ColumnC).Value, ParticipantNumber) = 0 Then
Rows(RowNumber).Select
    Selection.Delete Shift:=xlUp

Else: GoTo NextParticipant

End If

NextParticipant:
ParticipantNumber = ParticipantNumber + 1

If ParticipantNumber = 170 Then GoTo NextRow
End If

Loop

NextRow:
RowNumber = RowNumber + 1


Loop

End Sub

Note: I know that the GoTo function is evil, but I didn't come up with a way to work around it until now.

I hope I've explained myself clearly.

Thanks in advance for your help!

Cheers, J

解决方案

Your logic is almost ok, but .find is a much quicker way to get matches. Also, deleting rows while trying to keep track of row numbers is impossible unless you start from the bottom up. Try this instead. It will create a new worksheet and coppy all your good rows to the new sheet. Make sure your active worksheet is the correct one before running it.

Sub copyNOTdelete()
Dim ParticipantNumber As Long, RowNumber As Long
Dim wsMain As Worksheet, WSnew As Worksheet, newRowNumber As Long

Set wsMain = ActiveSheet
Set WSnew = Sheets.Add
wsMain.Activate

RowNumber = 2
newRowNumber = 1
ParticipantNumber = 101

For ParticipantNumber = 101 To 170
    With wsMain.Range("c2:c2733")
        Set c = .Find(CStr(ParticipantNumber), LookIn:=xlValues)
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                If wsMain.Range("D" & Right(c.Address, 1)) = ParticipantNumber Then
                    WSnew.Rows(newRowNumber).EntireRow.Value = wsMain.Rows(Right(c.Address, 1)).Value
                    newRowNumber = newRowNumber + 1
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
Next ParticipantNumber
End Sub

这篇关于Excel宏：迭代行和组合循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！