VBA SUM and Subtotal

[freeveil]

Hi, Struggling with VBA. Managed to do the basics of what I am trying to achieve, which is gather distances for my logistics company. I used google API to determine road mileage between locations, to determine the distance travelled on a job and then the subsequent empty running to get to the next job.

What I would like to do next is SUM the loaded mileage and the empty mileage together to correctly price the job, and then Subtotal each week's loaded, empty and total miles respectively to build an average earning report.

What is the best way to achieve this? I'm guessing there is a way to do this using VBA, but I was thinking a simpler solution would be extrapolating the dynamic data generated from the API into a static excel cell?

Below is an example of one weeks data, I have been doing one month per sheet so either 4 or 5 tables per sheet to accumulate this data from. Row 5 is a manual entry an example of what I am trying to achieve, the #values are just errors because i donbt have accurate address information further along the table.

Thanks,

RTK Work 2024.xlsm
	S	T	U
3	Job Miles	Empty Running	Total Miles
4	222 mi	3.0 mi	#VALUE!
5	497	42	539
6	174 mi	44.0 mi	#VALUE!
7	194 mi	#VALUE!	#VALUE!
8	#VALUE!	#VALUE!	#VALUE!
9	60.7 mi	1 ft	#VALUE!
10	#VALUE!	#VALUE!	#VALUE!
11	#VALUE!	#VALUE!	#VALUE!
12	#VALUE!	49.5 mi	#VALUE!
13	191 mi	Error	#VALUE!
14	#VALUE!	#VALUE!	#VALUE!
TRIAL

Cell Formulas
Range	Formula
S4,S6:S13	S4	=GetDistance([@[From GetDistance Ref]],[@[To GetDistance Ref]])
T4,T6:T13	T4	=GetDistance([@[To GetDistance Ref]], X5)
U4:U13	U4	=[@[Job Miles]]+[@[Empty Running]]
S14	S14	=SUBTOTAL(109,[Job Miles])
T14	T14	=SUBTOTAL(109,[Empty Running])
U14	U14	=SUBTOTAL(109,[Total Miles])

 

[freeveil]

Still needing help with this.

 

[PeteWright]

Please help me understand, you gather data with the Google API (how does that work?) and you need to sum up the data, in simple words.
I'm a little bit confused. It seems that you already found the answer when I look at your table.

You could add an IFERROR function to bypass the errors and a SUMPRODUCT function to sum the data per each week.

Maybe you could give another example showing a table with sample data.
Plus, color the cells with "static" data or Google API data let's say green and the cells that you need to be calculated in let's say yellow.
But that's just an idea...

 

[freeveil]

RTK Work 2024.xlsm
	B	C	D	E	F	G
2
3	From	To	Job Miles	Empty Running	Total Miles	Information
4	A Made up Place, Birmingham	A Made up Place, Glasgow	290	47	337	This row has been made up manually, with static information.
5	Edinburgh	Liverpool	221 mi	34.4 mi	#VALUE!	These rows are dynamic, the data pulled from google maps API.
6	Manchester	Inverness	374 mi	1 ft	#VALUE!
7	Inverness	Droitwich	462 mi	Error	#VALUE!
8			290	47	#VALUE!
9			Notice these totals only recognise the static information
TRIAL (2)

Cell Formulas
Range	Formula
F4:F7	F4	=[@[Job Miles]]+[@[Empty Running]]
D5:D7	D5	=GetDistance([@From],[@To])
E5:E7	E5	=GetDistance([@To],B6)
D8	D8	=SUBTOTAL(109,[Job Miles])
E8	E8	=SUBTOTAL(109,[Empty Running])
F8	F8	=SUBTOTAL(109,[Total Miles])

Google API code
Function
GetDistance(origin As String, destination As String) As String
Dim apiKey As String
Dim url As String
Dim responseText As String
Dim objHTTP As Object
Dim distance As String

' Replace "YOUR_API_KEY" with your actual API key
apiKey = "YOUR_API_KEY"
url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & origin & "&destinations=" & destination & "&units=imperial&key=" & apiKey
' Send HTTP request to the Distance Matrix API
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", url, False
objHTTP.send
responseText = objHTTP.responseText
' Parse the JSON response to extract distance
If InStr(responseText, "OK") > 0 Then
distance = Split(Split(responseText, """text"" : """)(1), """")(0)
Else
distance = "Error"
End If

' Return the distance
GetDistance = distance
End Function

 

[freeveil]

Thanks for the reply, I have attempted to clarify what I am looking for with my last post.

I enter location details into the FROM and TO columns. Google Maps API automatically finds the distances between those locations and then the subsequent travelling from TO to the next row's FROM.
These added together give me the total miles travelled on that Job.

On occassion I do need to enter manually static information, so I need to be able to add both static and dynamic information to find out all distances travelled over a week/month/year.