VBA COUNTIF w/ Last Line Variable

Betherski

New Member
Joined
Oct 6, 2009
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
I am struggling with syntax and have tried many different variations. Here is the formula I am trying to get to:
=COUNTIFS(E12:E425,"High")

I typed this in and recorded macro. I then wanted to modify to replace the line number w/ the last line of data.
So I updated my macro to this:
ActiveCell.FormulaR1C1 = "=COUNTIFS(E12:E" & LastRow & ",""High"")"

It comes out in my spreadsheet like this:
=COUNTIFS('E12':'E425',"High")

I don't know why I am getting the extra quotes around the range values. I have used this type of syntax to pull in the last line in many different formulas with no problem, but the COUNTIFS isn't working. I have tried so many variations. What am I doing wrong?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Since you are entering a A1 reference style formula, you should use the Formula property instead...

VBA Code:
ActiveCell.Formula = "=COUNTIFS(E12:E" & LastRow & ",""High"")"

Hope this helps!
 
Upvote 0
Solution
OMG. I knew it had to be something simple. Thank you SO much! This has been driving me crazy!!!
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,466
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top