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

On Apr 3, 6:58*am, Ed from AZ wrote:
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