Iterate rows, add like values - an easier way?

[Ed from AZ]

I am creating an "input form" - a range of cells for drivers to enter
their daily routes and miles. The route is selected through a Data
Validation in-cell drop down. The same route can be travelled more
than once per day. A Command Button and macro will write this data
into the appropriate worksheets.

I would like to add all the miles for each route to make one entry.
The way I see it, that means the macro must iterate all the rows and
maybe use a Select Case to add all the miles up depending on the route
selected. Is there an easier way, perhaps with a formula in the
worksheet? Or maybe an event triggered when the route is selected
from the Validation list?

Ed

 

[Ed from AZ]

I am creating an "input form" - a range of cells for drivers to enter
their daily routes and miles.  The route is selected through a Data
Validation in-cell drop down.  The same route can be travelled more
than once per day.  A Command Button and macro will write this data
into the appropriate worksheets.

I would like to add all the miles for each route to make one entry.
The way I see it, that means the macro must iterate all the rows and
maybe use a Select Case to add all the miles up depending on the route
selected.  Is there an easier way, perhaps with a formula in the
worksheet?  Or maybe an event triggered when the route is selected
from the Validation list?

Ed

After a bit of thought, I hit upon a much easier way. I set up
columns with the forumula "IF(A1 = "Route1",B1,""), copied that down,
and then copied it across and changed the route numbers. I set up a
SUM formula at the end of each column, so I get the total of the
individual route miles at those cells. Now I look in only seven cells
for my data, rather than iterating through 70 cells.

Ed