Range count average .

[Sunline]

Hello all ,

In this workbook , I have 3 x macros that are range counting cols BJ , BL , BM , one macro for each .

These work fine , they are able to look in col F that there are so many cells in this range defined by the number in col F .

Col F number is random .

I also have a macro that I did have working many years ago that could average a range . Now it stops on this row highlited in yellow .

MyAverage = Application.WorksheetFunction.Average(Myrange)

Can not find an old sheet that the average macro worked in , so here for any advise . Buttons up the top . Cols U V W have these macros results .

Thanks . Cant seem to get Wikifortio to download my sheet , anyone know of another .

 

[Sunline]

Unable to send a sheet so will do it this way .

sheet1.xlsm
	A					F			I	J	K	L									U	V	W																																							BJ		BL		BN
1	ID					Fsz			R1	R2	R3										R1	R2	R3																																							1p		2p		3p
2	1					7															42	28	35																																							1		6		9
3	2					7															42	28	35																																							12		3		7
4	3					7															42	28	35																																							8		2		2
5	4					7															42	28	35																																							4		2		5
6	5					7															42	28	35																																							5		3		2
7	6					7															42	28	35																																							2		5		2
8	7					7															42	28	35																																							10		7		8
9	8					12															27	21	11																																							3		2
10	9					12															27	21	11																																							4		5		2
11	10					12															27	21	11																																							9		3		5
12	11					12															27	21	11																																							5		11		4
13	12					12															27	21	11
14	13					12															27	21	11																																							3
15	14					12															27	21	11
16	15					12															27	21	11
17	16					12															27	21	11
18	17					12															27	21	11
19	18					12															27	21	11
20	19					12															27	21	11																																							3
21	20					12															76	51	76																																							7		2		10
22	21					12															76	51	76																																							4		6		9
23	22					12															76	51	76																																							5		4		2
24	23					12															76	51	76																																							3		11		9
25	24					12															76	51	76																																							5		P		11
26	25					12															76	51	76																																							4		6		6
27	26					12															76	51	76																																							8		4		9
28	27					12															76	51	76																																							15		4
29	28					12															76	51	76																																							9		2		7
30	29					12															76	51	76																																							6		7		3
31	30					12															76	51	76																																							10		5		10
32	31					12															76	51	76
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W1	Cell Value	<5	text	NO
W1	Cell Value	<5	text	NO
W1	Cell Value	<5	text	NO
K1	Cell Value	<5	text	NO
K1	Cell Value	<5	text	NO
K1	Cell Value	<5	text	NO

Sub SubTotalingBJ()
Dim WS As Worksheet
Dim LR As Long
Dim i As Long
Dim RptCount As Long
Dim MySum As Double
Dim Myrange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set WS = ActiveSheet
LR = WS.Range("F" & Rows.Count).End(xlUp).Row

i = 2
Do
If i > LR Then Exit Do
If RptCount = 0 Then
RptCount = WS.Cells(i, 6)
Set Myrange = WS.Range("BJ" & i & ":BJ" & i + RptCount - 1)
MySum = Application.WorksheetFunction.Sum(Myrange)

End If
WS.Cells(i, 9) = MySum
i = i + 1
RptCount = RptCount - 1
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub