How to use a cell formula with a lot of quotes in VBA

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
Hi, here is the exact formula I am using in a cell, it works perfectly.

=" #""Filtered Rows1"" = Table.SelectRows(#""Sorted Rows"", each true),"

How can I assign the above formula to a cell using VBA?

I tried the obvious:
Sheets("Sheet1").Range("A36").Formula = "=" #""Filtered Rows1"" = Table.SelectRows(#""Sorted Rows"", each true),""

But I get errors.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
One easy way to get the VBA code you need is to do the following:
Turn on the Macro Recorder, and then record yourself manually entering the formula into a cell.
Then stop the Macro Recorder, and look at the VBA code you just recorded. It will show you what that formula would look like, being entered through VBA.
 

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
One easy way to get the VBA code you need is to do the following:
Turn on the Macro Recorder, and then record yourself manually entering the formula into a cell.
Then stop the Macro Recorder, and look at the VBA code you just recorded. It will show you what that formula would look like, being entered through VBA.

So easy, I can't believe it. Almost forgot the macro recorder even exists. THANKS!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
It comes in real handy in these instances. Let Excel do all the heavy lifting!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,089
Members
416,010
Latest member
NJT

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
Top