I have items in column G on Sheet1 and I am trying to find all
corresponding customer names on Sheets("sheet1"). On this sheet with
the customers the item numbers are in column a and the customers are
in column B. An item may be listed more then once on the
Sheets("Sheet1") becasue there are more then one customer listed. I
am trying to go through each item in Sheet1 and concatenate all
customers that have that item number from Sheets("Sheet1"). Sheet1
and Sheets("Sheet1") are two different sheets in the same workbook.
Here is the code I have so far. It tells me I do not have a loop in
place. Huh?

Thanks,
Jay

Sub findLast()
Dim i
Dim lstRow As Long
Dim strResult As String
Dim Concat As String
Dim TargetCell As Range

lstRow = Range("g65536").End(xlUp).Row + 1

For Each i In Sheet1.Range("G4:G" & lstRow)
Dim rngFound As String
On Error GoTo nXtI
rngFound = Sheets("Sheet1").Range("A:A").Find(i.Value, _
LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True).Address
MsgBox (rngFound)

Dim myC As String
myC = Sheets("Sheet1").Range("A2:A65536").Find(i.Value, , , , , _
xlPrevious).Address
MsgBox (myC)


Set TargetCell = Sheets("Sheet1").Range(rngFound)


Do
If TargetCell.Row < Sheets("Sheet1").Range(myC).Row + 1
Then
strResult = TargetCell.Offset(0, 1).Value
Else
If TargetCell.Row = Range(myC).Row Then
Concat = TargetCell.Offset(0, 1).Value
End If
Concat = strResult & ", " & Concat
Loop Until TargetCell.Row = Sheets("Sheet1").Range(myC).Row

MsgBox (Concat)

nXtI:
Next i
End Sub

Jay, You're short of an End If. See line in your code below starting with **.

"jlclyde" wrote:

... It tells me I do not have a loop in
place. Huh?

Thanks,
Jay

Sub findLast()
Dim i
Dim lstRow As Long
Dim strResult As String
Dim Concat As String
Dim TargetCell As Range

lstRow = Range("g65536").End(xlUp).Row + 1

For Each i In Sheet1.Range("G4:G" & lstRow)
Dim rngFound As String
On Error GoTo nXtI
rngFound = Sheets("Sheet1").Range("A:A").Find(i.Value, _
LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True).Address
MsgBox (rngFound)

Dim myC As String
myC = Sheets("Sheet1").Range("A2:A65536").Find(i.Value, , , , , _
xlPrevious).Address
MsgBox (myC)


Set TargetCell = Sheets("Sheet1").Range(rngFound)


Do
If TargetCell.Row < Sheets("Sheet1").Range(myC).Row + 1
Then
strResult = TargetCell.Offset(0, 1).Value
Else
If TargetCell.Row = Range(myC).Row Then
Concat = TargetCell.Offset(0, 1).Value
** End If
End If
Concat = strResult & ", " & Concat
Loop Until TargetCell.Row = Sheets("Sheet1").Range(myC).Row

MsgBox (Concat)

nXtI:
Next i
End Sub

On Oct 1, 10:10*am, Lionel H
wrote:
Jay, You're short of an End If. See line in your code below starting with **.





"jlclyde" wrote:
... *It tells me I do not have a loop in
place. *Huh?

Thanks,
Jay

Sub findLast()
* * Dim i
* * Dim lstRow As Long
* * Dim strResult As String
* * Dim Concat As String
* * Dim TargetCell As Range

* * lstRow = Range("g65536").End(xlUp).Row + 1

* * For Each i In Sheet1.Range("G4:G" & lstRow)
* * * * Dim rngFound As String
* * On Error GoTo nXtI
* * rngFound = Sheets("Sheet1").Range("A:A").Find(i.Value, _
* * * * LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True).Address
* * MsgBox (rngFound)

* * Dim myC As String
* * myC = Sheets("Sheet1").Range("A2:A65536").Find(i.Value, , , , , _
* * * * xlPrevious).Address
* * MsgBox (myC)

* * Set TargetCell = Sheets("Sheet1").Range(rngFound)

* * * * Do
* * * * * * If TargetCell.Row < Sheets("Sheet1").Range(myC).Row + 1
Then
* * * * * * * * strResult = TargetCell.Offset(0, 1).Value
* * * * * * Else
* * * * * * * * If TargetCell.Row = Range(myC).Row Then
* * * * * * * * * * Concat = TargetCell.Offset(0, 1).Value

** * * * * * * * *End If



* * * * * * End If
* * * * * * Concat = strResult & ", " & Concat
* * * * Loop Until TargetCell.Row = Sheets("Sheet1").Range(myC).Row

* * * * MsgBox (Concat)

nXtI:
* * Next i
End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

There was some more tweaking that I needed to do, but you were right
as soon as I addded the end if it was off and runnign again.
Thanks,
Jay