Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Loop / Array / Ranges
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Loop through ranges | Excel Programming | |||
How to loop through all ranges in a worksheet | Excel Programming | |||
loop to name ranges | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |