Expanding Ranges when using R1C1 Notation

[BillTony]

Hi there,

I need to be able to using a dynamically expanding range and am having some trouble with a formula using R1C1 notation with a COUNTIF statement.

Basically, I need to base my countif on the number of rows with content in column A.

You can see that in the code below that the range is now limited to 10,420 rows - that's what I'm trying to get around.

Thanks in advance - hope the question was clear enough!

'My variable for the row count in column A.
Last_Row_ColA = Range("A" & Rows.Count).End(xlUp).Row
 
'Count the number of occurrences of the text string.
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[10420]C[-2],""*MSP*"")"

 

[Fluff]

Try

    activecell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[" & Last_Row_ColA & "]C[-2],""*MSP*"")"

 

[BillTony]

Thanks, that's REALLY close.

But, for some reason, when I do an F2 or Trace Precedents while standing in the cell the formula is entered in, it shows that is reading down 2 extra rows past the last row of column A.

Unfortunately, within the same data set I also do a count for multiple items that do not the contain the MSP string - this then picks up the 2 blank rows and over-counts in that instance...

 

[Fluff]

In that case, how about

Last_Row_ColA = Range("A" & Rows.Count).End(xlUp).Row
Range("J2").FormulaR1C1 = "=COUNTIF(RC[-2]:R[" & Last_Row_ColA - 2 & "]C[-2],""*MSP*"")"

 

[BillTony]

Ah yes, can't see the forest for the trees...

Thanks - GREATLY APPRECIATED!

 

[Fluff]

Glad to help & thanks for the feedback

 

[becci.gott]

This might sound nuts.. but do you actually need to do this in VBA? You could do similar using a named range which would then change dynamically if you added rows to the dataset.

If you go to Formulas >> Name Manager
Create a New Range (I've called mine "DynamicRange") and have it set up as below:
=OFFSET(Sheet1!J$2,0,-2,COUNTA(Sheet1!$A:$A)-2,1)

This will always pull in the range of data that is 2 columns prior to your current cell.

Then, in your cell J2, you can use the formula =COUNTIF(DynamicRange,"*MSP")

All without having to do anything in VBA.

 

[BillTony]

The question pertained to one small segment of an extremely large routine entirely written in VBA.

Thus...