Add PERCENTRANK Function Via Macro Code With Range Variable Address

I hope I can make this clear.

I have been doing great with VBA as long as I can express my cells and ranges using the Cells(i,j) format in my code. Recently I wanted to use some ready made functions in excel like AVG, PERCENTRANK, STDV, and I noticed that I can't get these functions to accept my ranges which are defined using the cells(i,j) format.

Example:

Set rng1 = Range(Cells(21, 7), Cells(40, 7))
    
    Cells(21, 8) = "=PERCENTRANK(G21:G40,G21)"

works fine
while

Set rng1 = Range(Cells(21, 7), Cells(40, 7))
    
    Cells(21, 8) = "=PERCENTRANK(rng1,cells(21,7))"

doesn't

Anything that I'm missin?

I'm very impressed with the help that I have been finding here and I'm sure this one will be no exception

Thanks Folks

Re: Rranges In Vba

These super fast replies make me believe that the world is still a nice place to live.

Many many thanks folks,

Marino