Looping using left function resulting in Concatenate

Hello Everyone - I am trying to produce a loop that loops through a vertical columm picking up the cells string that starts with "AGL" and puting an "&" on the end and untill it reaches a blank column. The best i have come up with is but not working, can anyone help?

Sub FindNum()
Dim strNumb As String
Dim lastrow As String

lastrow = ActiveSheet.UsedRange.Rows.Count
For Each cell In ActiveSheet.Range("K6:K" & lastrow)

   If cell(Left(cell, 1) = "EGL") Then
    If strNumb = "" Then
        strNumb = cell.Offset(0, 1)
        Else
            strNumb = strNumb & "&" & cell.Offset(0, 1)
        End If
    End If
Next


End Sub

can anyone help please


thanks
Jay

Hi jaimie

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers

You Could try this

Sub FindNum()
    Dim LastRow As String
    Dim Cell As Range
        
    LastRow = Range("K" & Rows.Count).End(xlUp).Row
    For Each Cell In ActiveSheet.Range("K6:K" & LastRow)
        If Left(Cell, 3) = "EGL" Then
            Cell = Cell & "&" & Cell.Offset(0, 1)
        End If
    Next

End Sub

Without some data to work with it isn't clear what you expecting as a result

Hope this helps

Remember the Code Tags in Post #1

Originally Posted by Marcol

You Could try this

Sub FindNum()
    Dim LastRow As String
    Dim Cell As Range
        
    LastRow = Range("K" & Rows.Count).End(xlUp).Row
    For Each Cell In ActiveSheet.Range("K6:K" & LastRow)
        If Left(Cell, 3) = "EGL" Then
            Cell = Cell & "&" & Cell.Offset(0, 1)
        End If
    Next

End Sub

Without some data to work with it isn't clear what you expecting as a result

Hope this helps

Remember the Code Tags in Post #1

Thanks a lot for your help on the above, But becuase i didnt explain myself very well this isnt what i was looking for. So for example i have a column that looks like this

DGL2279378
BR2 Rev
DGL2279321
DGL2279327

I need the code to go down that column and concatenate all the ones that begin with DGL - so would have an output as follows

(i need the output in a cell say cell 1 column 1) output = DGL2279378 & DGL2279321 & DGL2279327


Thanks a lot for any help

Jaimie

To avoid further confusion post a sample workbook showing some typical data that you have in columns K & L and on a separate sheet in the same workbook, the result you expect.

Try this

Sub ConcatenateMultipleData()
    Dim LastRow As Long
    Dim Cell As Range
    Dim strCode As String, strConc As String
    
    Sheets("Raw Data").Activate
    LastRow = Range("F" & Rows.Count).End(xlUp).Row
    Range("E3:F" & LastRow).Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlYes, _
                            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                            DataOption1:=xlSortNormal
    
    For n = 4 To LastRow
        strConc = Range("F" & n)
        strCode = Left(strConc, 3)
        Do While Left(Range("F" & n + 1), 3) = strCode
            strConc = strConc & "&" & Range("F" & n + 1)
            n = n + 1
        Loop
        If InStr(1, strConc, "&") > 0 Then
            With Sheets("Output")
                If .Range("D" & Rows.Count).End(xlUp).Row < 5 Then
                    .Range("D5") = strConc
                Else
                    .Range("D" & .Range("D" & Rows.Count).End(xlUp).Row + 1) = strConc
                End If
            End With
        End If
    Next

End Sub

Your sample data doesn't show many variations of what your real data might look like.
I've taken a guess and added some "possibilities"

Best I can do based on info provided.

Hope this helps.