I am having trouble debugging this code. I am gettign an error on the
line rngFound =. It seems that when it gets to an i that is not in
Sheet1 in bugs out. Is there a way to skip down to E at this point?

Thanks,
Jay

Sub findLast()
Dim i
Dim lstRow As Long
Dim strResult As String
Dim strResult2 As String
Dim Concat As String
Dim concat2 As String
Dim TargetCell As Range
Dim TargetCell2 As Range
Dim rngFound As String

lstRow = Sheet1.Range("g65536").End(xlUp).Row

For Each i In Sheet1.Range("G4:G" & lstRow)

'Bugs out on this line
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)
concat2 = ""
Concat = ""
Do
If TargetCell.Row <= Sheets("Sheet1").Range(myC).Row Then
strResult = TargetCell.Offset(0, 1).Value
Set TargetCell = TargetCell.Offset(1, 0)
Else
If TargetCell.Row = Range(myC).Row Then
Concat = TargetCell.Offset(0, 1).Value
Set TargetCell = TargetCell.Offset(1, 0)
End If
End If
Concat = strResult & ", " & Concat
Loop Until TargetCell.Row = Sheets("Sheet1").Range(myC).Row +
1

Set TargetCell2 = Sheets("Sheet1").Range(rngFound)
Do
If TargetCell2.Row <= Sheets("Sheet1").Range(myC).Row Then
strResult2 = TargetCell2.Offset(0, 4).Value
Set TargetCell2 = TargetCell2.Offset(1, 0)
Else
If TargetCell2.Row = Range(myC).Row Then
Concat = TargetCell2.Offset(0, 4).Value
Set TargetCell2 = TargetCell2.Offset(1, 0)
End If
End If
concat2 = strResult2 & ", " & concat2
Loop Until TargetCell2.Row = Sheets("Sheet1").Range(myC).Row +
1
E:
i.Offset(0, 20) = Concat
i.Offset(0, 21) = concat2

Next i
End Sub

Instead of doing both the .find and .address in one step, try to do it in two:

Dim FoundCell as range
set foundcell = Sheets("Sheet1").Range("A:A").Find(i.Value, _
LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)

'now check to see if you found something
if foundcell is nothing then
msgbox "Not found"
'what should happen?
else
rngfound = foundcell.address
'rest of code
end if

jlclyde wrote:

I am having trouble debugging this code. I am gettign an error on the
line rngFound =. It seems that when it gets to an i that is not in
Sheet1 in bugs out. Is there a way to skip down to E at this point?

Thanks,
Jay

Sub findLast()
Dim i
Dim lstRow As Long
Dim strResult As String
Dim strResult2 As String
Dim Concat As String
Dim concat2 As String
Dim TargetCell As Range
Dim TargetCell2 As Range
Dim rngFound As String

lstRow = Sheet1.Range("g65536").End(xlUp).Row

For Each i In Sheet1.Range("G4:G" & lstRow)

'Bugs out on this line
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)
concat2 = ""
Concat = ""
Do
If TargetCell.Row <= Sheets("Sheet1").Range(myC).Row Then
strResult = TargetCell.Offset(0, 1).Value
Set TargetCell = TargetCell.Offset(1, 0)
Else
If TargetCell.Row = Range(myC).Row Then
Concat = TargetCell.Offset(0, 1).Value
Set TargetCell = TargetCell.Offset(1, 0)
End If
End If
Concat = strResult & ", " & Concat
Loop Until TargetCell.Row = Sheets("Sheet1").Range(myC).Row +
1

Set TargetCell2 = Sheets("Sheet1").Range(rngFound)
Do
If TargetCell2.Row <= Sheets("Sheet1").Range(myC).Row Then
strResult2 = TargetCell2.Offset(0, 4).Value
Set TargetCell2 = TargetCell2.Offset(1, 0)
Else
If TargetCell2.Row = Range(myC).Row Then
Concat = TargetCell2.Offset(0, 4).Value
Set TargetCell2 = TargetCell2.Offset(1, 0)
End If
End If
concat2 = strResult2 & ", " & concat2
Loop Until TargetCell2.Row = Sheets("Sheet1").Range(myC).Row +
1
E:
i.Offset(0, 20) = Concat
i.Offset(0, 21) = concat2

Next i
End Sub

--

Dave Peterson