Solved: multiple if formula and checkbox

[kroz]

Hey all,

I have this formula that i'm adding with vba to a cell:

[vba]
if a then
if b then
if c then
action1
else
action2
else
if c then
action3
else
action4
else
if b then
if c then
action5
else
action6
else
if c then
action7
[/vba]

I want to add checkboxes to my table so that the user can activate/deactivate a,b,c from the formula.
The way i see it i have two options: link the checkboxes to some cells and put more if's in the formula or use even more VBA on my file.
What do you guys think would be the best option ? I fear that adding MORE if's to my formula would make it ever more cumbersome..

[Bob Phillips]

Link the checkboxes to a cell and test those cells instead of a and so on.

[kroz]

Let me try to rephrase my problem. I have the following formula that i want to modify using variables:

[vba]
.formula=if(A,if(B,(if(C, action1, action2),if(C, action3, action4)),if(B,(if(C, action5, action6),if(C, action7, action8)))
[/vba]
What i'm trying to achieve with checkboxes is this:

[vba]
if ckbx1 = false then
if ckbx2 = false then
if ckbx3 = false then
.value = "nothing to compare"
else
.formula = if(C,action7, action8)
else
if ckbx3 = false then
.formula = if(B,action6, action8)
else
.formula=if(B,if(C, action5, action6), if(C, action7, action8))
[/vba]
... and so on

the idea i got so far is to create strings with pieces of formula and activate/deactivate them using a macro but it will take some time to find the right combination.

[Bob Phillips]

Change A, B, C, etc to the actual cell references.

[kroz]

This is what i really wanted:
[vba]
Function test(a3 As Boolean, b3 As Boolean, c3 As Boolean)
Dim oneCell As Range, rg As Range
Dim ll As Integer
Dim a1 As String, b1 As String, c1 As String
Dim a2 As String, b2 As String, c2 As String
Dim action1 As String, action2 As String, action3 As String
Dim action4 As String, action5 As String, action6 As String
Dim action7 As String, action8 As String
rRow = Range("A9").End(xlDown).Row
For Each oneCell In Range("f9", Cells(rRow, 6)).Cells
a1 = "--($A$9:$A" & oneCell.Row & "=$A" & oneCell.Row & "),"
b1 = "--($b$9:$b" & oneCell.Row & "=$b" & oneCell.Row & "),"
c1 = "--($C$9:$C" & oneCell.Row & "=$C" & oneCell.Row & ")"
rFormula = "=SUMPRODUCT(" & a1 & b1 & c1 & ")< 2"

Cells(oneCell.Row, 7).Formula = rFormula
oneCell.Font.ColorIndex = 1
oneCell.Font.Bold = False
a2 = "AND(RC5<>"""",OR(RC5>R6C16,RC5<R6C15))"
b2 = "ISNA(RC4)"
c2 = "AND(RC2<>"""",ISNUMBER(RC2))"
action1 = "Error in PID/Error in MODULE ID/Error in Material"
action2 = "Error in PID/Error in MODULE ID"
action3 = "Error in PID/Error in Material"
action4 = "Error in PID"
action5 = "Error in MODULE ID/Error in Material"
action6 = "Error in MODULE ID"
action7 = "Error in Material"
action8 = "OK"
If a3 Then
If b3 Then
If c3 Then
oneCell.FormulaR1C1 = _
"=IF(" & a2 & ",IF(" & b2 & "," & vbLf & _
" IF(" & c2 & ",""" & action1 & """,""" & action2 & """)," & vbLf & _
" IF(" & c2 & ",""" & action3 & """,""" & action4 & """))," & vbLf & _
" IF(" & b2 & ",IF(" & c2 & ",""" & action5 & """,""" & action6 & """)," & vbLf & _
" IF(" & c2 & ",""" & action7 & """,""" & action8 & """)))"
Else
oneCell.FormulaR1C1 = _
"=IF(" & a2 & ",IF(" & b2 & ",""" & vbLf & _
action2 & """,""" & action4 & """)," & vbLf & _
" IF(" & b2 & ",""" & action6 & """,""" & action8 & """))"
End If
Else
If c3 Then
oneCell.FormulaR1C1 = _
"=IF(" & a2 & ",IF(" & c2 & ",""" & vbLf & _
action3 & """,""" & action4 & """)," & vbLf & _
" IF(" & c2 & ",""" & action7 & """,""" & action8 & """))"
Else
oneCell.FormulaR1C1 = _
"=IF(" & a2 & ",""" & action4 & """,""" & action8 & """)"
End If
End If
Else
If b3 Then
If c3 Then
oneCell.FormulaR1C1 = _
"=IF(" & b2 & ",IF(" & c2 & ",""" & vbLf & _
action5 & """,""" & action6 & """)," & vbLf & _
" IF(" & b2 & ",""" & action7 & """,""" & action8 & """))"
Else
oneCell.FormulaR1C1 = _
"=IF(" & b2 & ",""" & action6 & """,""" & action8 & """)"
End If
Else
If c3 Then
oneCell.FormulaR1C1 = _
"=IF(" & c2 & ",""" & action7 & """,""" & action8 & """)"
Else
oneCell.Value = "Nothing to compare!"
End If
End If
End If
Next
End Function
Sub retest()
Dim a As Boolean, b As Boolean, c As Boolean
a = Range("E6").Value
b = Range("D6").Value
c = Range("C6").Value
Application.ScreenUpdating = False
Call test(a, b, c)
Application.ScreenUpdating = True
End Sub
[/vba]

ignore the sumproduct part, i modify that as a bonus

[Bob Phillips]

Is that a question or a statement?

[kroz]

Is that a question or a statement?

Sorry to disappear like that, i left for the weekend. It was a statement, that was the solution to my problem. I started by trying to break down my formula using variables and then recompose it to suite my needs but i gave up. I ended up with this piece of code that compares A, B, C columns and gives you an action accordingly.