shoaibs
asked on
VBA Run-Time Error 13
Hi all,
When I input the following code, I get runtime error 13, type mismatch upon running it. This only occurs when the referenced cells (F8,H8,G8,I8) are blank. If I do input numbers into those cells, the code runs fine. Any ideas why this may be? Thanks.
Private Sub worksheet_deactivate()
If Evaluate("IF(AND(AND(OR((( F8-I8)/I8> 0.2),((F8- I8)/I8<-0. 2)),NOT(IS BLANK(I8)) ,NOT(ISBLA NK(F8))),A ND(NOT(ISB LANK(F8)), NOT(ISBLAN K(G8)),NOT (ISBLANK(H 8)),NOT(SU M(G8+H8)=F 8))),TRUE, FALSE)") Then
MsgBox "Test", vbOKCancel
End If
End Sub
When I input the following code, I get runtime error 13, type mismatch upon running it. This only occurs when the referenced cells (F8,H8,G8,I8) are blank. If I do input numbers into those cells, the code runs fine. Any ideas why this may be? Thanks.
Private Sub worksheet_deactivate()
If Evaluate("IF(AND(AND(OR(((
MsgBox "Test", vbOKCancel
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dear shoaibs,
the problem is caused because the formula has this F8-I8)/I8>0.2),((F8-I8)/I8 <-0.2)),
and if the cells or blank excel can not calulate..
You have to test before if the cells are empty and if not you can calculate the rest..
Kind regards
Eric
the problem is caused because the formula has this F8-I8)/I8>0.2),((F8-I8)/I8
and if the cells or blank excel can not calulate..
You have to test before if the cells are empty and if not you can calculate the rest..
Kind regards
Eric
ASKER
Hi Sid,
Thanks for the response. I'm using the popup message as a validation of sorts. When I use your code, the textbox pops up even if the cells are blank, I need the msgbox to only appear if the evaluate statement is true. Any ideas on how to accomplish this?
Thanks.
Thanks for the response. I'm using the popup message as a validation of sorts. When I use your code, the textbox pops up even if the cells are blank, I need the msgbox to only appear if the evaluate statement is true. Any ideas on how to accomplish this?
Thanks.
Did you try Rory's code?
Sid
Sid
ASKER
Rory's code works great, my next question is how would I go about adding additional evaluates to that e.g. the same formula but for the row below it. I want one single msgbox to popup if any of the rows (I have about 10) make the formula true.
Thanks.
Thanks.
>>"my next question is ..."
And hence, should be your next question... :)
And hence, should be your next question... :)
BTW, are these formulas currently in use as CF formulas for the cells you want to test?
ASKER
Yes, they are used as CF formulas
So which cells are you actually wanting to test?
Open in new window
Sid