shoaibs
asked on
Adding multiple Evaluates to
I got this excellent answer (below) to a previous question and it works like a charm. My question is how would I add more Evaluates to this. e.g. the same formula but for the row below it. I want a single msgbox to popup if any of the rows (I have about 10) make the formula true.
Thanks.
Thanks.
Private Sub worksheet_deactivate()
Dim varReturn
varReturn = Evaluate("IF(AND(AND(OR(((F8-I8)/I8>0.2),((F8-I8)/I8<-0.2)),NOT(ISBLANK(I8)),NOT(ISBLANK(F8))),AND(NOT(ISBLANK(F8)),NOT(ISBLANK(G8)),NOT(ISBLANK(H8)),NOT(SUM(G8+H8)=F8))),TRUE,FALSE)")
If Not IsError(varreturn) Then
If varReturn Then MsgBox "Test", vbOKCancel
End If
End Sub
Try if this code works
You have to modify the value of
StartRow=8
EndRow=18
as you need
You have to modify the value of
StartRow=8
EndRow=18
as you need
Private Sub worksheet_deactivate()
Dim varTotalResult
Dim StartRow
Dim EndRow
Dim varReturn
Dim t
StartRow=8
EndRow=18
varTotalResult=False
For t=StartRow to EndRow
varReturn = Evaluate("IF(AND(AND(OR(((F" & Trim(t) & "-I" & Trim(t) & ")/I" & Trim(t) & ">0.2),((F" & Trim(t) & "-I" & Trim(t) & ")/I" & Trim(t) & "<-0.2)),NOT(ISBLANK(I" & Trim(t) & ")),NOT(ISBLANK(F" & Trim(t) & "))),AND(NOT(ISBLANK(F" & Trim(t) & ")),NOT(ISBLANK(G" & Trim(t) & ")),NOT(ISBLANK(H" & Trim(t) & ")),NOT(SUM(G" & Trim(t) & "+H" & Trim(t) & ")=F" & Trim(t) & "))),TRUE,FALSE)")
If Not IsError(varreturn) Then
If varReturn Then varTotalResult=true
End If
Next
'Final Message
If varTotalResult Then MsgBox "Test", vbOKCancel
End Sub
ASKER
@ Rory
Not exactly sure how to do that- any chance I could get a little more insight? Thanks.
Not exactly sure how to do that- any chance I could get a little more insight? Thanks.
Something like:
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("A1:A10")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
End If
Next rngCell
End Sub
ASKER
Thanks Rory- when I specify the range (F8: F15), I get "Compile Error _ Next Without For"
Do I need to change anything other than this line? I apologize for my lack of knowledge in VBA - this is my first time working with it.
Do I need to change anything other than this line? I apologize for my lack of knowledge in VBA - this is my first time working with it.
For Each rngCell In Range("A1:A10")
There is a missing end if in the rorya code
try this
try this
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("A1:A10")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
end if
End If
Next rngCell
End Sub
Thanks - overlooked that!
or better, with your range
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("F8:F15")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
end if
End If
Next rngCell
End Sub
ASKER
Hmm, for both of those solutions, I get "Runtime Error 9- Subscript out of Range"
Any ideas why?
Any ideas why?
Do you definitely have conditional firmats applied to the range you are checking?
ASKER
Yes, there are three different conditional formats. If any of the cells are blank, will that throw this version off as well?
No. Can you post a sample sheet with the code you are using?
ASKER
Here you go Sample.xlsm
You have included F15 in your range, but it has no CF applied to it.
Try this one
I have checked the error in the case you include a cell without CF and I've noticed that you should replace ";" with "," in the evaluate formula.
There is another problem: this solution works only if the language is English because the .formula1 property returns the condition translated in the current language, but the Evaluate function pretends only English input.
It is a very stupid thing that you have to input Excel's formulas translated in your country language, this makes hard to convert formulas for international customers support
I have checked the error in the case you include a cell without CF and I've noticed that you should replace ";" with "," in the evaluate formula.
There is another problem: this solution works only if the language is English because the .formula1 property returns the condition translated in the current language, but the Evaluate function pretends only English input.
It is a very stupid thing that you have to input Excel's formulas translated in your country language, this makes hard to convert formulas for international customers support
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still having errors?
Which ranges are you checking? You just need to loop and check the CF formula directly rather than trying to tweak the formula string in code.
Rory