0

I am fairly new at programming. I feel like this should be a simple fix but I cannot find anything that works. I am trying to use a Select Case structure to write a different formula to a cell depending on the selected case. If I type:

Sheet1.Range("g10").Value = "=IF(SUM(F10)>0,SUM((F10-15)),"")"

I get an error:

Run-time error '1004'
Application-defined or object-defined error.

I can get it to work if I include spaces like so:

Sheet1.Range("g10").Value = " =IF(SUM(F10)>0,SUM((F10-15)),"") "

but then Excel puts it into the cell like text, not as a formula (it doesn't do anything).

I have tried a few different methods but have run into the same issue. Is it possible to do this?

I apologize if this has been asked and answered, but I wasn't able to find anyone referencing this specific problem.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
Murray Unger
  • 13
  • 1
  • 6
  • 1
    (a) If you are writing a formula, use the `Formula` property. (The `Value` property will **sometimes** work, but be safe and use the right property.) (b) Double-quotation mark characters within a string literal need to be escaped - so you need `Sheet1.Range("g10").Formula = "=IF(SUM(F10)>0,SUM((F10-15)),"""")"` – YowE3K Jun 12 '17 at 21:51
  • 3
    Possible duplicate of [Excel (2007) VBA - .Formula with quotes in it](https://stackoverflow.com/questions/10142448/excel-2007-vba-formula-with-quotes-in-it). Also duplicate of [How do I put double quotes in a string in vba?](https://stackoverflow.com/q/9024724/6535336), and [How to create strings containing double quotes in Excel formulas?](https://stackoverflow.com/q/216616/6535336) and lots more. – YowE3K Jun 12 '17 at 21:58
  • Thank you, I did not find those results in my previous searches. I will strive to be more diligent! – Murray Unger Jun 13 '17 at 15:33

3 Answers3

0

Wrong property. You need .formula not .value

worksheets("sheet1").range("g10").formula = "=IF(SUM(F10)>0,SUM((F10-15)),"")"
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • You still need to fix the double-quotation marks - at the moment the code will generate a formula of `=IF(SUM(F10)>0,SUM((F10-15)),")` which isn't a valid Excel formula. – YowE3K Jun 12 '17 at 21:54
0

You don't need to sum a single cell ie SUM(F10) Also SUM((F10-15)) is the same as F10-15.

Try this: Sheet1.Range("g10").Formula = "=IF(F10>0,F10-15,"""")" Also note, you needed to double the double quotes towards the end.

Note, you can do this on a range too like this:

Sheet1.Range("g10:g20").Formula = "=IF(F10>0,F10-15,"""")" and it is smart enough to increment the references for you for the 10 cells.

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
0

You should be able to type out the formula and then turn on the Macro Recorder and double-click on the cell with the forums, then turn off the Macro recorder and examine your code. That should give you what you want.

ASH
  • 20,759
  • 19
  • 87
  • 200