If Countif VBA where the countif range varies

[BeachSoccerG]

I have a sheet where Column A can range in size, with some blank cells within the range.
I have a table in the same sheet. The table starts at Column AN and it's range of rows as well as columns also can vary, though for my purposes right now, the number of columns in the table doesn't matter.

I have used the following to successfully find the last row for both column A as well as the table.

column_number = 1
last_row = Cells(Rows.Count, column_number).End(xlUp).Row

column_number2 = 39
last_row2 = Cells(Rows.Count, column_number2).End(xlUp).Row
' Display the value of the last non-blank cell
MsgBox "Last Row of Column A: " & last_row
MsgBox "Last Row of Column AM: " & last_row2

Next, I move on to If + Count_If, the results of which go into Column AK (which will have the same last-row2 as AM):

Range("AK2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF((Cells(2, 1), Cells(last_row, 1)),Table1[@[Division Name]]),"""",""NO""))"

The non-VBA formula is =IF(COUNTIF(Table1[@[Division Name]],""),"",IF(COUNTIF($A$2:$A$353,Table1[@[Division Name]]),"","NO")) and it works great! I just can't convert it to VBA to accommodate the fact that "353" will not always be the row count.

I can successfully select ranges of variable size, for example
Range(Cells(2, 1), Cells(last_row, 1)).Select

... and even fill down using a similar variable range reference:
Range("AK2").Select
Selection.AutoFill Destination:=Range(Cells(2, 37), Cells(last_row2, 37)), Type:=xlFillDefault

On my sample sheet photos, attached, the green cells use the non-VBA formula that works great other than it being inflexible for a changing range size.
The cells with the red font (in col AK) used the faulty VBA resulting in #NAME? error.

 

[Peter_SSs]

I have a table in the same sheet. The table starts at Column AN

I am a bit lost anyway from your description but the above does not seem to agree withy your image

 

[kvsrinivasamurthy]

Try replacing

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF((Cells(2, 1), Cells(last_row, 1)),Table1[@[Division Name]]),"""",""NO""))"

by

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF(A2:A" & last_row & ",Table1[@[Division Name]]),"""",""NO""))"

 

[BeachSoccerG]

Try replacing

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF((Cells(2, 1), Cells(last_row, 1)),Table1[@[Division Name]]),"""",""NO""))"

by

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF(A2:A" & last_row & ",Table1[@[Division Name]]),"""",""NO""))"

Thanks. I'd tried that before, and just tried again, ... it's still resulting in the #NAME? error.

 

[Alex Blakenburg]

Edit scratch this: You need the Address to be absolute referencing.

I am not convinced that formula is doing what you think it is doing, which I suspect Peter is alluding to as well but you can use @kvsrinivasamurthy's formula just change the left hand side to Formula2 (it's an array formula)

ActiveCell.Formula2 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF(A2:A" & last_row & ",Table1[@[Division Name]]),"""",""NO""))"

 

[Peter_SSs]

Ah, Alex has just beaten me to it to some extent but your problem is that your code says your are inserting a formula in R1C1 format but the formula is not in that format.
You can input it in standard format as Alex has suggested but if you want to use R1C1 format then it would be

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF(R2C1:R" & last_row & "C1,Table1[@[Division Name]]),"""",""NO""))"

Other comments though ..

• There is no need to select the cell to input the formula and selecting generally slows your code
• I'm guessing that you are subsequently going to fill that formula down the column somehow but you can put them all in at once (again without selecting) as follows

Range("AK2:AK" & last_row2).FormulaR1C1 = _
"=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF(R2C1:R" & last_row & "C1,Table1[@[Division Name]]),"""",""NO""))"

Yet another option would be to put this formula in AK2 and you wouldn't need to copy it down.

Range("AK2").Formula2 = _
"=IF(Table1[Division Name]="""","""",IF(COUNTIF(A2:A" & last_row & ",Table1[Division Name]),"""",""NO""))"

 

[Alex Blakenburg]

Peter has got you covered but if you needed to say use last_row multiple times in a formula the Replace method often makes for an easier conversion from the formula you have in the spreadsheet to what you need in VBA.
(Most people would do this all in one line but I find this clearer.)
Note: it didn't need formula2

Dim sFormula As String
' Placeholder for last_row = "~"
sFormula = "=IF(COUNTIF(Table1[@[Division Name]],""""),"""",IF(COUNTIF($A$2:$A$~" & ",Table1[@[Division Name]]),"""",""NO""))"
sFormula = Replace(sFormula, "~", last_row)
ActiveCell.Formula = sFormula

 

[BeachSoccerG]

Huge THANK YOU to the community team here! I am learning by self-study and practice on the job, and so thankful for this community helping me after spending hours trying to sleuth it myself. Yay, all of you!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

As a matter of interest, which of the three suggestions in the marked post did you end up using?