Dynamic AVERAGEIF in macro

[NoooC]

I'm writing a macro and I would like to use an AVERAGEIF with a dynamic selection instead of a fixed selection.

At the moment I use this in my macro:

AVERAGEIF('sheet1'!R5C11:R10000C11,RC[-1],'sheet1'!R5C12:R10000C12)"

The problem is now, if I have more then 10000 rows, they will not be included in my AVERAGEIF.

I have created a variable to know how many rows 'sheet1' are used:

Dim usedRangeSheet1 As Integer
    usedRangeSheet1 = Sheets("sheet1").usedRange.Rows.Count

How can I, in my code, change 10000 with usedRangeSheet1?

 

[davesweep]

Hi,

Does this help?

AVERAGEIF('sheet1'!R5C11:R10000C11,RC[-1],'sheet1'!R5C12:R" & usedRangeSheet1 & "C12)"

I'd be tempted to name the last row variable as something more appropriate, and you could use this to define it..

Dim iLastRow as Integer
iLastRow = sheets("Sheet1").cells(Sheets("Sheet1").rows.count,12).end(xlup).row

 

[NoooC]

Tnx!

Did not know I could use

..." & usedRangeSheet1 & "...