Seeking help to sum up certain data

[abberyfarm]

Hi there,


I have data for car journeys. Would anybody be able to help me out with a macro to calculate the remaining distance to travel for that date in Column G. Here is an example:

Excel Workbook
	A	B	C	D	E	F	G
1	start_date	start_time	end_date	end_time	Type	Trip distance	Remaining distance to travel
2	04/01/2012	08:19:52	04/01/2012	08:32:41	T	3.81	0
3	05/01/2012	08:13:11	05/01/2012	08:23:39	T	3.82	15.94
4	05/01/2012	17:45:29	05/01/2012	17:50:19	T	1.26	14.68
5	05/01/2012	18:05:45	05/01/2012	18:16:56	T	3.66	11.02
6	05/01/2012	18:43:32	05/01/2012	19:00:17	T	8.52	2.5
7	05/01/2012	19:10:06	05/01/2012	19:16:35	T	2.5	0
Sheet1

Many thanks

John

 

[texasalynn]

try this

Sub TripRemaining()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row + 1

SR = ActiveCell.Address
Range(SR).FormulaR1C1 = "=SUM(R[1]C[-1]:R" & LR & "C6)"
Range(SR).Copy Destination:=Range(SR & ":G" & LR - 1)

End Sub

note: this code is intended for the cursor to be in cell G3 when ran.

 

[abberyfarm]

Thank you very much for your help. Much appreciated!