Autosum specific rows and columns

[tezza]

Hi all,

Have a look at this:

RC Call Logs 17-06-24 to 19-07-24.xlsx
	F	G	H	I	J	K
1	Call Start Time	Call End Time	Call Duration	Real Start	Real End	Duration
2	10:00	16:00	06:00	09:45	17:56	08:11
3	10:00	16:00	06:00	09:47	15:40	05:53
4	10:00	16:00	06:00	09:51	15:53	06:02
5	10:00	16:00	06:00	09:51	15:47	05:56
6	10:00	16:00	06:00	09:46	15:58	06:12
7	10:00	16:00	06:00	09:58	15:49	05:51
8	10:00	16:00	06:00	09:46	15:50	06:04
9	10:00	16:00	06:00	09:58	15:55	05:57
10			48:00			50:06
11
12
13	09:45	12:45	03:00	09:38	12:43	03:05
14	09:45	12:45	03:00	09:39	12:46	03:07
15			06:00			06:12
16
17
18	11:30	13:30	02:00	11:25	13:29	02:04
19	11:30	13:30	02:00	11:25	13:32	02:07
20	11:30	13:30	02:00	11:25	13:25	02:00
21	11:30	13:30	02:00	11:25	13:16	01:51
22			08:00			08:02
23
24
25	09:30	12:30	03:00	09:27	12:15	02:48
26	10:30	13:30	03:00	10:29	13:28	02:59
27	10:45	12:45	02:00	10:40	12:40	02:00
28	09:30	12:30	03:00	09:26	12:19	02:53
29	10:30	13:00	02:30	10:26	13:05	02:39
30	09:30	12:30	03:00	09:16	12:18	03:02
31	10:45	12:45	02:00	10:40	12:41	02:01
32	09:30	12:30	03:00	09:24	12:11	02:47
33	10:30	13:30	03:00	10:24	13:21	02:57
34	10:45	12:45	02:00	10:40	12:39	01:59
35			26:30			26:05
36
37
Call_Logs

Cell Formulas
Range	Formula
H10,K10	H10	=SUM(H2:H9)
H15,K15	H15	=SUM(H13:H14)
H22,K22	H22	=SUM(H18:H21)
H35,K35	H35	=SUM(H25:H34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I1:I9999	Expression	=$M1="Check"	text	NO
J2:J9999	Expression	=I2>J2	text	NO
K1:K9999,B1:B9999	Expression	=AND(COUNTA(XEV1:D1)>1,$H1-$K1>TIME(0,15,0))	text	NO
H2:H9999	Expression	=(Q2*1440)>30	text	NO
A1:F9999	Expression	=$R1=$R2	text	NO

I'm looking for a bit of VBA coding that autosums each break in Col H and then copies that formula to Col K, same row as Col K doesn't always have a full list of times.

When you look at the Cell Formulas box above, that's what I'm looking to automate.

Can you help?

 

[DanteAmor]

Try this:

Sub autosum()
  Dim ar As Range, i As Long
  
  For Each ar In Range("F2", Range("F" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    i = ar.Cells(ar.Rows.Count).Row + 1
    With Range("H" & i & ",K" & i)
      .Formula = "=SUM(" & ar.Offset(0, 2).Address(0, 0) & ")"
    End With
  Next
End Sub

 

[tezza]

Try this:

Sub autosum()
  Dim ar As Range, i As Long
 
  For Each ar In Range("F2", Range("F" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants).Areas
    i = ar.Cells(ar.Rows.Count).Row + 1
    With Range("H" & i & ",K" & i)
      .Formula = "=SUM(" & ar.Offset(0, 2).Address(0, 0) & ")"
    End With
  Next
End Sub

Perfect, thank you.