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

 

[Joe4]

You only use the "Set" command when setting objects, like ranges.

If you only want the value, drop the word "Set", i.e.

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

If you want to set it to be a range variable, drop the ".Value", i.e.

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

 

[Fluff]

What are you trying to do? average(B2/G2)makes no sense as you would be averaging a single number.

 

[abdelfattah]

@Joe4 then the error changes to object variable or block variable not set

 

[Joe4]

When you hit "Debug", which line of code does it highlight?

Can you show us what the data that is in column B and cell G2 look like, and explain, in plain English, exactly how you want this to work.

 

[abdelfattah]

@Fluff After dividing a number by a number then should calculate average the result after dividing .

 

[abdelfattah]

Can you show us what the data that is in column B and cell G2 look like, and explain, in plain English, exactly how you want this to work.

I have ever mentioned in OP . they contain values(numbers).

 

[Fluff]

But after you have divided B2 by G2, you only have one value, so there is nothing to average.
If B2 is 20 & G2 is 10 B2/G2 is 2 & the average of 2 is 2

 

[Joe4]

I want to see your actual data and your expected result, so we can see what you are working with and clearly how you want this to work.
As they say, pictures say a thousand words (and your descriptions so far haven't been too helpful to Fluff or I in figuring out exactly how you want this to work).

 

[Anthony47]

And, anyway [Dim] myRangeC1 As Range is wrong