Arrays parameters in Functions

[Guest]

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 Laanemets]

Hi


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

or

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

 

[Bob Phillips]

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

Hi


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

or

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



--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

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

 

[Guest]

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

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

Hi


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

or

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



--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

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

 

[Bob Phillips]

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

Bob

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

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

Hi


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

or

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



--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )




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