Too difficult for me to understand as I do not have any basic in programming.

I still prefer a simple copy downwards formula to solve this kind of problem.

Thank you anyway for your contribution.

Kind Regards

Low





--
A36B58K641


"Don Guillett" wrote:

The macro I presented does not need any selections. It is designed to run,
on the ACTIVE SHEET, as written for data starting at row 2 with the vehicle
in col A and the meter in col B. The mileage will be placed in col C.....
Why is this so hard???

To run from somewhere else, just select the data sheet first
Sub SumByCategory()

sheets("Data").select

lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub

OR use WITH to run from anywhere without selections. Pls notice the
placement of the dots .

Sub PMOSumByCategory()'untested

with sheets("Data")
lr = .Cells(.Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until .Cells(mr, 2) = 0
mname = .Cells(mr, 1)
nr = .Columns(1).Find(mname, after:=.Cells(lr, 1), _
searchdirection:=xlPrevious).Row
.Cells(nr, 3) = .Cells(nr, 2) - .Cells(mr, 2)
mr = nr + 1
Loop
end with
End Sub



--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

After correcting the entry at cell A4, the formula now works fine :

Vehicle Meter Mileage
BBN2361 9,650 3150
BBN2361 10,600
BBN2361 12,800
BCA7114 3,200 7400
BCA7114 4,500
BCA7114 6,800
BCA7114 10,600
BFA2129 8,200 8700
BFA2129 10,200
BFA2129 14,800
BFA2129 16,900
BGA2367 12,500 4350
BGA2367 12,500
BGA2367 12,500
BGA2367 16,850
BGS9654 10,200 6000
BGS9654 11,200
BGS9654 14,200
BGS9654 16,200
BSK1400 8,500 8300
BSK1400 10,250
BSK1400 11,500
BSK1400 12,500
BSK1400 14,200
BSK1400 16,800
PLM3265 9,800 8700
PLM3265 10,200
PLM3265 14,200
PLM3265 16,100
PLM3265 18,500

However I still have problem with the macro.

I recorded the following macro as PMO:

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub

I selected range A1:C31 before running the macro. After clicking RUN,
processing began but error message "Run time error :
(-2147417848(80010128)'
- method of select of object range failed" came out. I clicked at debug
the
following codes came out:

ub PMO()
' PMO Macro
Sheets("Data").Select
Range("A35").Select
ActiveWindow.LargeScroll Down:=-1
Range("C2:C31").Select
Application.Run "PMO.xlsx!PMO"
Range("A2:C31").Select
End Sub

May I know what are my errors and how to fix this problem ?

Thanks

Low

--
A36B58K641


"Don Guillett" wrote:

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this
in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just
fine.
Your excel version_________?

I have not gotten any files from you.
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

I tried this out using a new data set of 31 rows. I modified your
formula
at
cell C2 to

=MAX(IF(A2:A31=A2,B2:B31))-MIN(IF(A2:A31=A2,B2:B31))
and =MAX(IF(A2:A$31=A2,B2:B$31))-MIN(IF(A2:A$31=A2,B2:B$31))

It did not work. The results are not correct as follows (using sorted
or
unsorted data):

Vehicle Meter Mileage
BBN2361 9,650 15300
BBN2361 10,600 0
BBN2631 12,800 0
BCA7114 3,200 7400
BCA7114 4,500 6100
BCA7114 6,800 3800
BCA7114 10,600 0
BFA2129 8,200 8700
BFA2129 10,200 6700
BFA2129 14,800 2100
BFA2129 16,900 0
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 16,850 0
BGS9654 10,200 6000
BGS9654 11,200 5000
BGS9654 14,200 2000
BGS9654 16,200 0
BSK1400 8,500 8300
BSK1400 10,250 6550
BSK1400 11,500 5300
BSK1400 12,500 4300
BSK1400 14,200 2600
BSK1400 16,800 0
PLM3265 9,800 8700
PLM3265 10,200 8300
PLM3265 14,200 4300
PLM3265 16,100 2400
PLM3265 18,500 0

Any suggestion on what errors that were made.

I entered formula using CTRL + SHIFT+ ENTER key.

Thanks


Low

Note : I used MS Excel 2002 at work place and have to try it at home
using
MS Ecel 2007 (Beta 2) as I do not have the former at home







--
A36B58K641


"Don Guillett" wrote:

Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the
last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and
copy
down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C
is
left
blank.


Thanks

Low



--