Help with Loop / Array / Ranges
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
--
Regards,
Tom Ogilvy
"Kathy - Lovullo" wrote in message
...
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.
|