当在范围内找到值后，每个循环在迭代中转到下一行。

Question

全,

下面的代码遍历列和行来查看IF语句是真的。它似乎在运行整个代码，返回重复的行。一旦找到了值，我希望这段代码转到下一行。

我不知道如何修改这段代码，但我想问题在于我为每个循环设置的通用代码，我已经设置了任何关于如何解决这个问题的建议，我将不胜感激。

Dim LR As Long
LR = Workbooks(trackerName).Sheets("Results").Range("A1048576").End(xlUp).Row
Dim LRC As Long
LRC = Workbooks(trackerName).Sheets("Columnsforbox").Range("A1048576").End(xlUp).Row + 1
For Each c In Workbooks(trackerName).Sheets("results").Range("A4:K" & LR)
    If c.Value = UserName Or c.Value = UserId Then
        Worksheets("Columnsforbox").Range("A" & LRC) = Worksheets("Results").Range("E" & c.Row)
        Worksheets("Columnsforbox").Range("B" & LRC) = Worksheets("Results").Range("D" & c.Row)
        Worksheets("Columnsforbox").Range("C" & LRC) = Worksheets("Results").Range("A" & c.Row)
        Worksheets("Columnsforbox").Range("D" & LRC) = Worksheets("Results").Range("B" & c.Row)
        Worksheets("Columnsforbox").Range("E" & LRC) = Worksheets("Results").Range("C" & c.Row)
        LRC = LRC + 1
    End If
Next c

Answer 1

基本相同，但现在我们遍历数组：

Dim myArr(), i as Long, j as Long
Dim LR As Long
LR = Workbooks(trackerName).Sheets("Results").Range("A1048576").End(xlUp).Row
Dim LRC As Long
LRC = Workbooks(trackerName).Sheets("Columnsforbox").Range("A1048576").End(xlUp).Row + 1
myArr = Range("A4:K" & LR).Value
For i = LBound(myArr,1) To Ubound(myArr,1)
    For j = LBound(myArr,2) To Ubound(myArr,2)
        If myArr(i,j) = UserName Or myArr(i,j) = UserId Then
            Worksheets("Columnsforbox").Range("A" & LRC) = Worksheets("Results").Range("E" & i)
            Worksheets("Columnsforbox").Range("B" & LRC) = Worksheets("Results").Range("D" & i)
            Worksheets("Columnsforbox").Range("C" & LRC) = Worksheets("Results").Range("A" & i)
            Worksheets("Columnsforbox").Range("D" & LRC) = Worksheets("Results").Range("B" & i)
            Worksheets("Columnsforbox").Range("E" & LRC) = Worksheets("Results").Range("C" & i)
            LRC = LRC + 1
            Exit For
        End If
    Next j
Next i

你有个主意。

Answer 2

另一种不用循环的解决方案。

Sub Demo()
    Dim rngUserName As Range, rngUserId As Range
    Dim LR As Long, LRC As Long, rowIndex As Long
    Dim srcSht As Worksheet, destSht As Worksheet

    Set srcSht = Workbooks(trackerName).Sheets("Results")       'this is source sheet
    Set destSht = Workbooks(trackerName).Sheets("Columnsforbox") 'this is destination sheet

    LR = srcSht.Cells(srcSht.Rows.Count, "A").End(xlUp).Row     'get last row using column A
    LRC = destSht.Cells(destSht.Rows.Count, "A").End(xlUp).Row  'get last row using column A

    Set rngUserName = Range("A4:K" & LR).Find(UserName, after:=Cells(4, 1), searchdirection:=xlPrevious) 'find user name
    Set rngUserId = Range("A4:K" & LR).Find(UserId, after:=Cells(4, 1), searchdirection:=xlPrevious)   'find user id

    If Not rngUserName Is Nothing And Not rngUserId Is Nothing Then 'if both user name & user id are found
        rowIndex = Application.Max(rngUserName.Row, rngUserId.Row)
    ElseIf Not rngUserName Is Nothing Then                          'if only user name found
        rowIndex = rngUserName.Row
    ElseIf Not Not rngUserId Is Nothing Then                        'if only user id found
        rowIndex = rngUserId.Row
    End If
    MsgBox rowIndex

    destSht.Range("A" & LRC) = srcSht.Range("E" & rowIndex)
    destSht.Range("B" & LRC) = srcSht.Range("D" & rowIndex)
    destSht.Range("C" & LRC) = srcSht.Range("A" & rowIndex)
    destSht.Range("D" & LRC) = srcSht.Range("B" & rowIndex)
    destSht.Range("E" & LRC) = srcSht.Range("C" & rowIndex)
End Sub