Solved

Adding multiple Evaluates to

Posted on 2011-02-11
17
254 Views
Last Modified: 2012-05-11
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.

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

Open in new window




0
Comment
Question by:shoaibs
  • 6
  • 6
  • 5
17 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34872282
Trying to confuse me now! ;)

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
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34872470
Try if this code works

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

Open in new window

0
 

Author Comment

by:shoaibs
ID: 34872711
@ Rory

Not exactly sure how to do that- any chance I could get a little more insight? Thanks.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34872769
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

Open in new window

0
 

Author Comment

by:shoaibs
ID: 34872933
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.

For Each rngCell In Range("A1:A10")

Open in new window

0
 
LVL 7

Expert Comment

by:kemi67
ID: 34872990
There is a missing end if in the rorya code
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34872994
Thanks - overlooked that!
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34873016
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

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:shoaibs
ID: 34873557
Hmm, for both of those solutions, I get "Runtime Error 9- Subscript out of Range"

Any ideas why?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34873694
Do you definitely have conditional firmats applied to the range you are checking?
0
 

Author Comment

by:shoaibs
ID: 34873762
Yes, there are three different conditional formats. If any of the cells are blank, will that throw this version off as well?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34873905
No. Can you post a sample sheet with the code you are using?
0
 

Author Comment

by:shoaibs
ID: 34874013
Here you go Sample.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34874330
You have included F15 in your range, but it has no CF applied to it.
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34877911
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
0
 
LVL 7

Accepted Solution

by:
kemi67 earned 500 total points
ID: 34877913
ops!
I've forgot the code:
Here you are
Private Sub worksheet_deactivate()
   Dim varReturn
   Dim rngCell As Range
   Dim FormatCond As FormatCondition
   Dim intErr As Integer
   
   On Error GoTo ResetError
   
   intErr = 0
   ' change range as needed
   For Each rngCell In Range("F8:F14")
      For Each FormatCond In rngCell.FormatConditions
          varReturn = Evaluate(Replace(Mid$(FormatCond.Formula1, 2), ";", ","))
          If intErr = 0 Then
            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
          Else
            intErr = 0
          End If
      Next
   Next rngCell
   Exit Sub

ResetError:
intErr = Err.Number
Resume Next
   
End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:kemi67
ID: 34918061
Still having errors?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now