Countif in VBA

[GordonLS]

Good afternoon. so I use the following to select the 1st empty cell in Column G via VBA.

Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).Select - In this example G10 is selected

I now need to count with the following:

=sumproduct(1/countif(G2:G9,G2:G9) - however I never know what the 1st empty cell is so in other words I want count down from G2 to the last cell prior to my formula.

G

1 Order Number

2 WALM-15181

3 WALM-14830

4 WALM-14797

5 WALM-14788

6 WALM-14738

7 WALM-14578

8 WALM-14290

9 WALM-14290

=sumproduct(1/countif( ??????? )

 

[Fluff]

How about

Range("G" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=sumproduct(1/countif(r2c:r[-1]c,r2c:r[-1]c))"

 

[GordonLS]

This works perfect. Can you guide me to where I can understand the following?

(r2c:r[-1]c,r2c:r[-1]c))"

 

[Fluff]

The R stands for row & the C for column. If the number is not in [] then it's absolute, otherwise it's relative, and if there is no number it means same column/row.
So r2c is row 2 this column & r[-1]c is one row above in this column

 

[GordonLS]

Very much appreciated. I will be back again.

Gordon

 

[Fluff]

You're welcome & thanks for the feedback.