CODEWITHSUNDEEP
vbaexcel
Kurter21
Kurter21

Reputation: 73

Set formula critera range to entire column VBA

I'm attempting to automate a report, (forgive my lack of Excel experience), and come across this error. Which displays #NAME in the cell. The code should select the cell and COUNTIF <1 for all of column E on another page in the workbook. Is this a simple syntax error?

    Range("B2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(prrSearchResults!E:E,""<""&1)"

Upvotes: 1

Views: 325

Answers (2)

Trum
Trum

Reputation: 630

You don't need to select the cell actually - and I would avoid R1C1 formulas for something simple.

As such - I'd just try this: This would put the formula in the cell

Sub test()
    Range("B2").Value = "=COUNTIF(prrSearchResults!E:E,""<""&1)"
End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Drop the R1C1

Sub qwerty()
    ActiveCell.Formula = "=COUNTIF(prrSearchResults!E:E,""<""&1)"
End Sub

Upvotes: 3

Related Questions