Help with Loop / Array / Ranges

[Guest]

I am having a little bit of trouble trying to figure out how summarize a
Expense report I am working on. In the main part of the report, the user may
enter a trip number many times to detail expenses. I need to summarize this
list in another section of the report, only listing the trip once.
Therefore I am working with 2 different range of cells, 1 with detailed trip
#'s that may be repeated and 1 with summary.

I have my VBA Code looping through the Detailed Trip #'s to determine that
the trip # is not equal to the last trip used or that it is not blank. If
that is the case the code will put the Trip# into the Summary section where
totals will be completed. My problem is that I am having trouble figuring
out how to look at the existing Summary Trip numbers to verify that it has
not already been used.

I am not sure if I am taking the right approach to this or not. Here is the
code that I have so far...


Sub TripTotals()

Dim strTrip,
Dim currCell As Variant

For Each currCell In Worksheets("Expense Report").Range _("E21:e41").Cells

If currCell <> strTrip And currCell <> "" Then
Set strTrip = currCell

'I need to verify here that this trip does not
'already exist in cells G43:G52 - HOW?

Range("G53").End(xlUp).Offset(1, 0).Value = strTrip

End If
Next
End Sub

Any assitance would be greatly appreciated.

 

[Tom Ogilvy]

Sub TripTotals()

Dim strTrip
Dim currCell As Variant

For Each currCell In Worksheets("Expense Report").Range _("E21:e41").Cells

If currCell <> strTrip And currCell <> "" Then
Set strTrip = currCell

'I need to verify here that this trip does not
'already exist in cells G43:G52 - HOW?
If application.Countif(Range("G43:G52"),strTrip) = 0 then
Range("G53").End(xlUp).Offset(1, 0).Value = strTrip
End If
End If
Next
End Sub