Macro line is red and I don't know why issue

[Darkshine31]

Hi good people of the Excel world.

I have a macro that I've built which is designed to "Reset" a sheet back to default settings once a client has had access to it and changed certain cell ranges as required.

I have a range of cells named "data_entry_low_grid_connection_prospects_speel" (which happens to be merged cells C36 - C39 in case you need to know). It has a working formula in it that, if overwritten by the client needs to be reset when the reset button is clicked in the sheet.

The formula is:

=IF(ISNUMBER(SEARCH("Please go", data_entry_low_grid_potential_question)), data_entry_low_grid_potential_question, "Please provide an explanation for low prospects of connection - see example - do not start with a capital letter or end with a full stop")

The macro entry related to that section is:

Range("data_entry_low_grid_connection_prospects_speel").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH("Please go", data_entry_low_grid_potential_question)), data_entry_low_grid_potential_question, "Please provide an explanation for low prospects of connection - see example - do not start with a capital letter or end with a full stop")"
I don't understand why the line is red and doesn't work in VBA when it does work fine in the sheet without the use of VBA.
Any assistance would be much appreciated.
Dean

 

[saboh12617]

Hello, the error is due to the quotes inside the formula. They "break" the big quote.

To put quotes into a string, you need to double them (from " to "").

For example

"=IF(Sheet1!A1=0,"""",Sheet1!A1)"

Is the correct way to write in a VBA string : =IF(Sheet1!A1=0,"",Sheet1!A1)

 

[Darkshine31]

Hello, the error is due to the quotes inside the formula. They "break" the big quote.

To put quotes into a string, you need to double them (from " to "").

For example

"=IF(Sheet1!A1=0,"""",Sheet1!A1)"

Is the correct way to write in a VBA string : =IF(Sheet1!A1=0,"",Sheet1!A1)

Aaaahhh!

Fantastic.

Thank you saboh12617 for your time

 

[saboh12617]

Glad to help, have a good day