Solved How to modify an Excel VBA formula

August 20, 2017 at 14:36:51
Specs: Windows 10 Enterprise
How do I modify the below VBA formula so both conditions need to be true in order for BD2 to be "X". Currently, it shows true (and autofills X) if either of the 2 conditions are true.

.Range("BD2").FormulaR1C1 = "=IF(AND(ISERROR(FIND(""BG"",RC59)),(ISERROR(FIND(""BH"",RC60)))),"""",""X"")"


See More: How to modify an Excel VBA formula

Reply ↓  Report •

#1
August 20, 2017 at 16:43:05
The formula is not much more than your basic IF function.

=IF(logical_test, value_if_true, value_if_false)

logical_test: AND(ISERROR(FIND("BG",RC59)),(ISERROR(FIND("BH",RC60))))
value_if_true: "" (Nothing)
value_if_false: "X"

Just swap the value_if_true argument with the value_if_false.

You'll get an X when the AND function returns TRUE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
August 21, 2017 at 12:20:49
Thanks for the response. It still isn't calculating correctly in my code, so I must have a different issue
causing the problem.

To try a different approach ... the below lines of code for BD2 and BE2 work independently but I need to
consolidate them into a single formula for BD2. So in order for BD2 to be "X", the formula should require a
condition in the first portion to be true PLUS, at least 1 condition in the second portion. (In other words,
the BE2 filter would be combined as an additional requirement for the existing BD2 filter.) I can't get the
syntax to work correctly when I am trying to merge them with an AND separator, are you able to help?

Here are the 2 sections, that need to be combined into the [.Range("BD2").FormulaR1C1]:


.Range("BD2").FormulaR1C1 = "=IF(ISERROR(FIND(""REFERENCE TO A STANDARD"",RC54)),IF(ISERROR(FIND(""CATEGORY: STANDARDS"",RC54)),IF(ISERROR(FIND(""EXCERPT FROM A PUBLIC STANDARD"",RC54)),IF(ISERROR(FIND(""STANDARD REFERENCE REVIEWED BY "",RC54)),IF(ISERROR(FIND(""PUBLIC STANDARD REVIEWED BY "",RC54)),"""",""X""),""X""),""X""),""X""),""X"")"

.Range("BE2").FormulaR1C1 = "=IF(ISERROR(FIND(""KRONOS"",RC54)),IF(ISERROR(FIND(""KHRONOS"",RC54)),IF(ISERROR(FIND("" MIPI "",RC54)),IF(ISERROR(FIND("" MIPI."",RC54)),IF(ISERROR(FIND(""MOBILE INDUSTRY PROCESSOR"",RC54)),IF(ISERROR(FIND("" OPENMAX "",RC54)),IF(ISERROR(FIND("" OPENMAX."",RC54)),IF(ISERROR(FIND("" OPENCL "",RC54)),IF(ISERROR(FIND("" OPENCL."",RC54)),IF(ISERROR(FIND("" OMX."",RC54)),IF(ISERROR(FIND("" OPENGL "",RC54)),IF(ISERROR(FIND("" OPENGL."",RC54)),IF(ISERROR(FIND("" OPENSL "",RC54)),IF(ISERROR(FIND("" OPENSL."",RC54)),IF(ISERROR(FIND("" WEBGL "",RC54)),IF(ISERROR(FIND("" WEBGL."",RC8))," & _
"IF(ISERROR(FIND("" OPENVG "",RC54)),IF(ISERROR(FIND("" OPENVG. "",RC54)),IF(ISERROR(FIND("" EGL "",RC54)), IF(ISERROR(FIND("" EGL. "",RC54)),IF(ISERROR(FIND(""USB"",RC8)),IF(ISERROR(FIND(""BIF"",RC8)),IF(ISERROR(FIND(""CSI"",RC8)),IF(ISERROR(FIND(""DSI"",RC8)),IF(ISERROR(FIND(""OST "",RC8)),"""",""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X""),""X"")"

  

Thanks very much for any help you can provide!


Reply ↓  Report •

#3
August 21, 2017 at 13:27:48
✔ Best Answer
Before I take a shot at your actual question, I've got a few questions of my own.

1 - Why are you using a bunch of Nested IFs instead of a single IF(AND...)?

Isn't the following an equivalent formula? IF BB2 contains any of those strings the formula will return X. Is that what you want (from that individual formula)?

=IF(AND(
ISERROR(FIND("REFERENCE TO A STANDARD",$BB2)),
ISERROR(FIND("CATEGORY: STANDARDS",$BB2)),
ISERROR(FIND("EXCERPT FROM A PUBLIC STANDARD",$BB2)),
ISERROR(FIND("STANDARD REFERENCE REVIEWED BY ",$BB2)),
ISERROR(FIND("PUBLIC STANDARD REVIEWED BY ",$BB2))),"","X")

2 - Do you actually need to put the formulas in the cells or can the conditions be tested within VBA and have VBA put the X in the cell when appropriate?

3 - After formulas are combined in BD2, will the second formula still exist in BE2 or are you trying to eliminate it completely?

4 - re: "So in order for BD2 to be "X", the formula should require a condition in the first portion to be true PLUS, at least 1 condition in the second portion."

Maybe my head is inside out because of all of the IF's and ISERROR's, but it looks to me that you need False conditions to get an X, not True conditions. What am I missing?

Maybe you could make my life easier and reduce those long formulas to just 2 or 3 clauses each, with shorter Find strings so that my tired brain can focus on the problem and not be distracted by a formula that contains over 1000 characters.

It's the concept we need to work on, not those long formulas.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question