I can't seem to get the syntax right on this VBA statement:

I want to use a similar statement to :
ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[1]C[6]:R[10]C[6] , R[1]C[6] )"

except that the array parameter "R[1]C[6]:R[10]C[6]" needs to be specified
using row and column variables.

Possibly something close to

ActiveCell.FormulaR1C1 = "=PERCENTRANK( ActiveSheet.Range(Cells(1, 6),
Cells(lastrow, 6)) , R[1]C[6] )"

Can anyone help me fix the syntax or offer another approach? Thanks

Hi


ActiveCell.Formula = "=PERCENTRANK(F1:F10, F1)"

or

ActiveCell.Formula = "=PERCENTRANK(F1:F" & NumericExpression & ", F1)"



--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )




"BruceK" wrote in message
...
I can't seem to get the syntax right on this VBA statement:

I want to use a similar statement to :
ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[1]C[6]:R[10]C[6] ,
R[1]C[6] )"

except that the array parameter "R[1]C[6]:R[10]C[6]" needs to be
specified
using row and column variables.

Possibly something close to

ActiveCell.FormulaR1C1 = "=PERCENTRANK( ActiveSheet.Range(Cells(1, 6),
Cells(lastrow, 6)) , R[1]C[6] )"

Can anyone help me fix the syntax or offer another approach? Thanks

Arvi showed you using Formula, but if you need to use FormulaR1C1 to provide
column and row variability, just build up bit by bit

ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[" & firstRow & "]C[" & firstCol &
"]:R[" & _
lastRow & "]C[" & lastCol & "] ,
R[" & firstRow & "]C[" & fisrCol & "] )"

just as an example to show the approach

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
Hi


ActiveCell.Formula = "=PERCENTRANK(F1:F10, F1)"

or

ActiveCell.Formula = "=PERCENTRANK(F1:F" & NumericExpression & ", F1)"



--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )




"BruceK" wrote in message
...
I can't seem to get the syntax right on this VBA statement:

I want to use a similar statement to :
ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[1]C[6]:R[10]C[6] ,
R[1]C[6] )"

except that the array parameter "R[1]C[6]:R[10]C[6]" needs to be
specified
using row and column variables.

Possibly something close to

ActiveCell.FormulaR1C1 = "=PERCENTRANK( ActiveSheet.Range(Cells(1, 6),
Cells(lastrow, 6)) , R[1]C[6] )"

Can anyone help me fix the syntax or offer another approach? Thanks

Thanks. Not really that hard, once you finally see the solution. I
appreciate the help.
Bruce


"Bob Phillips" wrote:

Arvi showed you using Formula, but if you need to use FormulaR1C1 to provide
column and row variability, just build up bit by bit

ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[" & firstRow & "]C[" & firstCol &
"]:R[" & _
lastRow & "]C[" & lastCol & "] ,
R[" & firstRow & "]C[" & fisrCol & "] )"

just as an example to show the approach

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
Hi


ActiveCell.Formula = "=PERCENTRANK(F1:F10, F1)"

or

ActiveCell.Formula = "=PERCENTRANK(F1:F" & NumericExpression & ", F1)"



--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )




"BruceK" wrote in message
...
I can't seem to get the syntax right on this VBA statement:

I want to use a similar statement to :
ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[1]C[6]:R[10]C[6] ,
R[1]C[6] )"

except that the array parameter "R[1]C[6]:R[10]C[6]" needs to be
specified
using row and column variables.

Possibly something close to

ActiveCell.FormulaR1C1 = "=PERCENTRANK( ActiveSheet.Range(Cells(1, 6),
Cells(lastrow, 6)) , R[1]C[6] )"

Can anyone help me fix the syntax or offer another approach? Thanks

Exactly, once you know the basics, you can take it a long way <g

Bob

"BruceK" wrote in message
...
Thanks. Not really that hard, once you finally see the solution. I
appreciate the help.
Bruce


"Bob Phillips" wrote:

Arvi showed you using Formula, but if you need to use FormulaR1C1 to
provide
column and row variability, just build up bit by bit

ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[" & firstRow & "]C[" &
firstCol &
"]:R[" & _
lastRow & "]C[" & lastCol &
"] ,
R[" & firstRow & "]C[" & fisrCol & "] )"

just as an example to show the approach

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
Hi


ActiveCell.Formula = "=PERCENTRANK(F1:F10, F1)"

or

ActiveCell.Formula = "=PERCENTRANK(F1:F" & NumericExpression & ", F1)"



--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )




"BruceK" wrote in message
...
I can't seem to get the syntax right on this VBA statement:

I want to use a similar statement to :
ActiveCell.FormulaR1C1 = "=PERCENTRANK( R[1]C[6]:R[10]C[6] ,
R[1]C[6] )"

except that the array parameter "R[1]C[6]:R[10]C[6]" needs to be
specified
using row and column variables.

Possibly something close to

ActiveCell.FormulaR1C1 = "=PERCENTRANK( ActiveSheet.Range(Cells(1,
6),
Cells(lastrow, 6)) , R[1]C[6] )"

Can anyone help me fix the syntax or offer another approach? Thanks