How can I dynamically enter the Average range in VBA for Excel?

Im using VBA to enter the average of a range of cells from a separate worksheet. I can enter code that copies a cell into the new worksheet, and also a separate line that enters the Average of a range. However I want it to enter range data dynamically and not a set range. Im unable to get this line to work.

Currently, The first line of my IF statement this finds matching data in the Master sheet, then in the cell offset by 5 columns of the Averages worksheet, enters the cell of the matching line in the Master sheet, offset by 2 columns.

The second line similarly finds the correct cell, and enters the average of D8:H8. (this line works FYI) However, I don't want it to always enter D8:H8 as it will always be moving, I want it to enter the Average of current rownumber, column 4 to column 8.

The third line is my attempt, however this is wrong and doesn't work. Ive tried various variations of this line but still doesn't work.

Would anybody understand how to enter the data for this?

ImportNumRows = Range("A6", Range("A6").End(xlDown)).Rows.Count
 ExportNumRows = Range("A18", Range("A18").End(xlDown)).Rows.Count
 
 'Dim counter As Integer
 
 
'Start of loop
 For counter = 6 To 5 + ImportNumRows
 Set rng = Worksheets("Averages").Cells(counter, 1)
 Set rng2 = Worksheets("Master").Columns("A:A").Find(What:=rng.Value, MatchCase:=True)
   If Not rng2 Is Nothing Then
    rownumber = rng2.Row
    
    rng.Offset(0, 5) = Worksheets("Master").Cells(rownumber, 2).Value
    rng.Offset(0, 6) = WorksheetFunction.Average(Worksheets("Master").Range("D8:H8"))
    rng.Offset(0, 7) = WorksheetFunction.Average(Worksheets("Master").Range(rownumber, 4:rownumber, 8))
    
    Else
    End If
    
'End of loop
Next counter```


</details>


# 答案1
**得分**: 0

以下是翻译好的部分：

```plaintext
Instead of:

    Worksheets("Master").Range(rownumber, 4:rownumber, 8)

Try:

    Worksheets("Master").Range(Cells(rownumber, 4), Cells(rownumber, 8))

Edit:

    Worksheets("Master").Range(Worksheets("Master").Cells(rownumber, 4), Worksheets("Master").Cells(rownumber, 8))

or 

    With Worksheets("Master")
        Rng.Offset(0, 7) = WorksheetFunction.Average(.Range(.Cells(rownumber, 4), .Cells(rownumber, 8)))
    End With

Worksheets("Master").Range(rownumber, 4:rownumber, 8)

Worksheets("Master").Range(Cells(rownumber, 4), Cells(rownumber, 8))

Worksheets("Master").Range(Worksheets("Master").Cells(rownumber, 4), Worksheets("Master").Cells(rownumber, 8))

With Worksheets("Master")
    Rng.Offset(0, 7) = WorksheetFunction.Average(.Range(.Cells(rownumber, 4), .Cells(rownumber, 8)))
End With