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

 

[Fluff]

@Anthony47 in what way is that wrong?

 

[abdelfattah]

@Fluff you right but , I note there is a little simple when shows the value . for instance if the B2 =542, G2=3 based on the formula in OP =181
and if use just B2/G2 . it will give 180.6667

 

[abdelfattah]

@Joe4 forgive me , but why you want picture, the column B and cells G2 are number and the result in column C

 

[Fluff]

The average of 180.6667 is 180.6667 but it will show as 181 if you have the cell set to display no decimal places.

 

[Anthony47]

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

If myRangeC1 is a "range", will this line be correct?
(in response to @Fluff )

 

[GWteB]

@abdelfattah, not sure what you want to achieve, but afaik worksheet functions cannot handle Range objects within VBA in this way.
See whether this is what you're after.

Sub abdelfattah()

        Dim nb_rows As Long
        Dim myRangeC As Range
        Dim i As Long

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

        Set myRangeC = Worksheets("Sheet1").Range("B2:B" & nb_rows)
        
        For i = 2 To nb_rows
                Range("C" & i).Value = Excel.Application.Evaluate("=Average(" & myRangeC.Address & "/" & Range("G" & i).Value & ")")
        Next i
End Sub

 

[Fluff]

If myRangeC1 is a "range", will this line be correct?

No, but that does not mean that the Dim statement is wrong.

 

[abdelfattah]

@GWteB what means this error #VALUE! in column B?

 

[GWteB]

No idea ...

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

... so you were supposed to have numbers in column B. The code I provided puts the results in column C as requested.

 

[Fluff]

Can you please explain exactly what you want the code to do?