CODEWITHSUNDEEP
vbaexcelfor-loop
Sean Bailey
Sean Bailey

Reputation: 375

For each loop to go to next row in iteration once value found in range

All,

I have the below code which iterates through columns and rows to see IF the statement is true. It seems to be running through the whole code bringing back duplicate rows. I would like this code to go to the next row once a value has been found.

I'm unsure how to adapt this code but I imagine the issue lies with the general for each loop I have set up any advise on how to fix this would be much appreciated. 

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

Upvotes: 1

Views: 2164

Answers (2)

Mrig
Mrig

Reputation: 11702

Another solution without using loop.

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

Upvotes: 0

AntiDrondert
AntiDrondert

Reputation: 1149

Basicly the same, but now we loop through array: 

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

Well, you got an idea.

Upvotes: 1

Related Questions