find, copy and paste function

[mani_singh]

i have the following code which looks at a list of references on design sheet col J then goes to design1 sheet finds the same ref anywhere cols A to K if found it goes along that row to col O copies the data and pastes in on design sheet sam row but col K

this all works however there are multiple instances of the ref in design 1 sheet so multiple results are returned, what i need is the macro to paste the word GD: in front of only the first result not in front of all results.

any ideas?

Sub DesignMacroGD2()
LastRow = Sheets("design").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design").Cells(i, 10)
With Sheets("design1")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design ").Cells(i, 11) = Sheets("design").Cells(i, 11) + "GD: " .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

Don't you just need to exit the loops when a match is found? Try the untested:

Sub DesignMacroGD2()
    Dim Found As Boolean
    LastRow = Sheets("design").Cells(Rows.Count, 10).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 26 To LastRow
        Target = Sheets("design").Cells(i, 10)
        With Sheets("design1")
            For J = 2 To 54
                If Found = True Then
                    Found = False
                    Exit For
                End If 
                For K = 4 To 11
                    If Found = True Then
                        Exit For
                    End If 
                    If Target = .Cells(J, K) Then
                        Sheets("design ").Cells(i, 11) = Sheets("design").Cells(i, 11) + "GD: " .Cells(J, 15) + " | "
                        Found = True
                    End If
                Next K
            Next J
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mani_singh]

no essentially on design1 there are multilple instances of the ref value from design, each with unique field to copy across.

so what i have now is (output)

GD: abcd | GD: efgh | GD: ijkl

what i need is

GD: abcd | efgh | ijkl

 

[Andrew Poulsom]

Maybe try:

Sub DesignMacroGD2()
    Dim First As Boolean
    LastRow = Sheets("design").Cells(Rows.Count, 10).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 26 To LastRow
        First = True
        Target = Sheets("design").Cells(i, 10)
        With Sheets("design1")
            For J = 2 To 54
                For K = 4 To 11
                    If Target = .Cells(J, K) Then
                        If First = True Then
                            Sheets("design ").Cells(i, 11) = Sheets("design").Cells(i, 11) & "GD: " & .Cells(J, 15) & " | "
                            First = False
                        Else
                            Sheets("design ").Cells(i, 11) = Sheets("design").Cells(i, 11) & " " & .Cells(J, 15) & " | "
                        End If
                    End If
                Next K
            Next J
        End With
        First = True
    Next i
    Application.ScreenUpdating = True
End Sub