conversion of COUNTIF into R1C1 style

[near]

Hi, guys i have written the COUNTIF formula in A1 style, now i want to use it in macro but in R1C1 style. I am trying from last 3 hours but can't do it.
here is the formula, Please help me to convert it into R1C1 reference style.

"=COUNTIF(B1:P1,"">=""&Param!$B$1)"

Thanks...

 

[pgc01]

Hi

The conversion A1 -> R1C1 is only independent if you have an absolute address. For a relative address you must supply the cell where the formula is written.

In your case

$B$1 -> R1C2


B1:P1 -> R[x]C[y], where x and y depend on the cell where the formula is, x is the distance in rows and y the distance in columns.

If you have difficulties, post the cell where the formula is written

 

[p45cal]

It very much depends what cell(s) you're putting the formula in. So which?

 

[pgc01]

P. S. Another question would be: why do you want to change the notation?

 

[near]

I want to change the notation, because i am already using a macro which is using R1C1 notation. Now i want to include this formula in this macro. Actually i am calculating the average and difference of rows in my earlier macro. now i want to use the countif to calculate the no-of values which are greater than some threshold value. I have calculated that by directly putting the formula, but actually it should be written in a macro.
Here is my macro:

Sub Avg()
    Dim x As Long, y As Long, z As Long, HeaderRow As Long
    HeaderRow = 1
    With Range("a1").CurrentRegion
        x = .Rows.Count
        y = .Columns.Count
        With .Offset(x)
            z = .Row
            .Cells(1).Value = "Avg"
            .Cells(2, 1).Resize(x - HeaderRow).Formula = _
            "=r" & z & "c&""-""&r[-" & x - HeaderRow + 1 & "]c"
 'Problem is here in upcoming 2 rows :          
           '.Cells(1, 17).Resize(m - HeaderRow - z).Formula = _
           '  "=COUNTIF(B5:P5,"">=""&Param!$B$1)"
                        
            With .Cells(2).Resize(1, y - 1)
                .Formula = _
                "=average(r" & 1 + HeaderRow & "c:r[-1]c)"
                .Offset(1).Resize(x - HeaderRow).Formula = _
                "=r" & z & "c-r[-" & x - HeaderRow + 1 & "]c + 1"
            End With
        End With
    End With
End sub

My data have 16 columns and 4 rows (including header row).

 

[near]

I want to add one thing more, the COUNTIF formula will be applied into the 17th column. Now the macro which i have provided in my last post works fine, but i manually have to provide the range in countif (B5:P5). I want R1C1 form so i will not need put the range manually

 

[p45cal]

First, this line:
.Cells(2, 1).Resize(x - HeaderRow).Formula = "=r" & z & "c&""-""&r[-" & x - HeaderRow + 1 & "]c"

You seem to be using R1C1 notation, but you havent used .FormulaR1C1.

Second, in the line:
.Cells(1, 17).Resize(m - HeaderRow - z).Formula = "=COUNTIF(B5:P5,"">=""&Param!$B$1)"
you haven't defined m, unless it's a global variable.
And will you always want the ftopmost cell with the formula in to refer to row 5?
Perhaps that B5:P5 needs to be $B5:$P5

Perhaps, just perhaps, you need:
....FormulaR1C1 = "=COUNTIF(RC2:RC16,"">=""&Param!R1C2)"

 

[near]

Wow....Great......That's what I want...Its working....Thank you so much p45cal.

 

[near]

Please tell me also, how to write this statement in R1C1 ref style,

=Q5/15*100

I am little confused in this notation.
NOTE: I have to write this formula next column to countif formula.i.e., R5

 

[p45cal]

This is dead easy; record yourself pasting =Q5/15*100 into cell R5. I got this:

    Range("R5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/15*100"
    Range("R6").Select

You don't need the last line, and the first two can be shortened to:

    Range("R5").FormulaR1C1 = "=RC[-1]/15*100"