error object required to use function average

[abdelfattah]

hello
I try using average function but it gives object error in this line

Set myRangeC1 = Worksheets("Sheet1").Range("G2").Value

the column B contains values and range G2 also values and the formula should be like this average(B2/G2) and the result should be in column C
any idea to fix it ,please?

Sub Test()

        Dim nb_rows As Long
        Dim myRangeC, myRangeC1 As Range
        Dim i As Long

        nb_rows = Range("b" & Rows.Count).End(xlUp).Row

        'Change Sheet1 to your sheet name
        Set myRangeC = Worksheets("Sheet1").Range("B2:B" & nb_rows)
        Set myRangeC1 = Worksheets("Sheet1").Range("G2").Value

        For i = 2 To nb_rows
                Range("C" & i).Value = Application.WorksheetFunction.Average(myRangeC / myRangeC1)
        Next i

End Sub

 

[abdelfattah]

@GWteB this is what I got based on the code . the same values in column C for different values in column B

1 AVERAGE.xlsx
	B	C	D	E	F	G
1	NUM	AVER				CONDITION
2	542	23,335				3
3	7,552	23,335
4	56,622	23,335
5	63,632	23,335
6	70,642	23,335
7	77,652	23,335
8	84,662	23,335
9	91,672	23,335
10	98,682	23,335
11	105,692	23,335
12	112,702	23,335
Sheet1

and should change

Range("G" & i).Value

to

Range("G2").Value

 

[abdelfattah]

should be

1AVERAGE.xlsx
	B	C	D	E	F	G
1	NUM	AVER				CONDITION
2	542	181				3
3	7,552	2,517
4	56,622	18,874
5	63,632	21,211
6	70,642	23,547
7	77,652	25,884
8	84,662	28,221
9	91,672	30,557
10	98,682	32,894
11	105,692	35,231
12	112,702	37,567
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=AVERAGE(B2/$G$2)

 

[GWteB]

I see ... No offence intended, but that makes no sense to me since the results will always be the same (so there's also no loop needed).
You're wanting to get the average of a range consisting of multiple cells. If you divide the outcome with a certain value, you can do that twice or 1,000 times, you're always getting the same result.

EDIT: responding on post #21

 

[GWteB]

Well, that has nothing to do with averages, but with division:

Book1
	A	B	C	D	E	F	G
1		NUM	AVER				CONDITION
2		542	180.6667	180.666667			3
3		7552	2517.333	2517.33333
4		56622	18874	18874
5		63632	21210.67	21210.6667
6		70642	23547.33	23547.3333
7		77652	25884	25884
8		84662	28220.67	28220.6667
9		91672	30557.33	30557.3333
10		98682	32894	32894
11		105692	35230.67	35230.6667
12		112702	37567.33	37567.3333
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=AVERAGE(B2/$G$2)
D2:D12	D2	=B2/$G$2

 

[abdelfattah]

@GWteB

but that makes no sense to me

also @Fluff said it . I thought the result could be different . after what you said and @Fluff I will satisfy so .

my apologies if this thread is not sense from the beginning .

thanks for your guiding and advices .

 

[GWteB]

You are welcome and thanks for letting us know