VBA not inputting Excel formula

0
votes

I've been stuck on this for the past couple of hours and it's driving me nuts. I'm trying to enter this formula in cell B2:

=IF(OR(C2="Yes",D2="Yes",E2="Yes",F2="Yes",G2="Yes",H2="Yes",I2="Yes"),"Yes",IF(AND(C2="No",D2="No",E2="No",F2="No",G2="No",H2="No",I2="No"),"No"))

The formula works fine but no formula is entered into the cell when run through a VBA macro.

I recorded the macro, copy-and-pasted the output, and the cell still remains blank. Here is the output from the recording:

Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(OR(RC[1]=""Yes"",RC[2]=""Yes"",RC[3]=""Yes"",RC[4]=""Yes"",RC[5]=""Yes"",RC[6]=""Yes"",RC[7]=""Yes""),""Yes"",IF(AND(RC[1]=""No"",RC[2]=""No"",RC[3]=""No"",RC[4]=""No"",RC[5]=""No"",RC[6]=""No"",RC[7]=""No""),""No""))"

I've also tried Range("B2").Formula, without success.

Any suggestions? Thanks!

3
vbaexcel
FYI your IF Statement is really long and you can eleminate the second part of your statement as the result would be the same =IF(OR(C2="Yes",D2="Yes",E2="Yes",F2="Yes",G2="Yes",H2="Yes",I2="Yes"),"Yes","No") Only making your Formula smaller to make debugging easier. This is ofcourse unless you want to add another condition at the end after the last NO. So the formula reads if any of the cells are Yes then return a Yes else if ALL of the cells are NO then return a NO. (can be achieved with the shortened formula included in this comment) - izzymo
When you say "The cell remains blank", do you mean that there is no formula in the cell after your code runs, or that the formula is there but it is evaluating to a (blank) value? - FreeMan
@izzymo Great, thanks for that tip. Just tried it and the output is the same! - EricY
@FreeMan There is no formula in the cell after the code runs. It works fine when input manually in Excel. I believe the problem lies in the syntax of the script. I'm thinking it has to do with the quotation marks. - EricY
Found the problem guys! It wasn't the formula/code. I was trying to move one cell to the left, go down to the last cell in the column containing a value, move to the right, select all the way up to the cell with the formula (B2), and fill down. Apparently it didn't select all the way up, it just moved there. B1 was empty so it filled that down. Should've debugged better. I was dead tired at the time. Thanks for the help, everyone. - EricY

3 Answers

0
votes

If all the cells referenced are blank, you should be getting a 'FALSE' result.

Your if starts with (OR(C2="Yes",D2="Yes",E2="Yes",F2="Yes",G2="Yes",H2="Yes",I2="Yes") if any of these = Yes then return a Yes

however, they are blank, so go on and do this bit IF(AND(C2="No",D2="No",E2="No",F2="No",G2="No",H2="No",I2="No"),"No")

they are still blank, and give FALSE - with the second if you are saying all the cells must be NO to give a NO return

can you post the data around the cell you are using?

0
votes

You could also use this formula - count how many are Yes and count how many are No.

=IF(COUNTIF($C$1:$I$1,"Yes")>=1,"Yes",IF(COUNTIF($C$1:$I$1,"No")=7,"No"))

You still have the problem if there's a blank, or a value other than Yes or No.

If 5 say 'Yes' and 1 says 'Bob' it will return 'Yes', but if 6 says 'No' and 1 says 'Bob' it will return FALSE.

You need to say what happens if the second part isn't TRUE:

=IF(OR(C2="Yes",D2="Yes",E2="Yes",F2="Yes",G2="Yes",H2="Yes",I2="Yes"),"Yes",IF(AND(C2="No",D2="No",E2="No",F2="No",G2="No",H2="No",I2="No"),"No",**"Neither Yes or No"**))

or

=IF(COUNTIF($C$1:$I$1,"Yes")>=1,"Yes",IF(COUNTIF($C$1:$I$1,"No")=7,"No","**Neither Yes or No**"))

or as @izzymo said - if at least one is Yes then return Yes, otherwise return No:

=IF(COUNTIF($C$1:$I$1,"Yes")>=1,"Yes","No")

0
votes

An IF statement has 3 parts:

  • the condition
  • the "if true"
  • the "if false".

Your first statement has all three:

  • if (any of a bunch of cells is 'yes')
  • 'Yes'
  • another IF statement*

The problem is in "another IF statement", where you have:

  • if (a bunch of cells are 'no')
  • 'No'
  • nothing...

IF(AND(C2="No",D2="No",E2="No",F2="No",G2="No",H2="No",I2="No"),"No", ???put a false condition here???)

* Or, you can do as izzymo suggested in the comment and replace "another IF statement" with "No", and end up with the same result