# Excel 2010 formula, How do I write this formula? (VBA)

I need a bit of help from the more experienced Excel VBA users.

Basically I have 2 groups of 3 cells (6 cells total) If one group of 3 cells is filled in it needs to accept it and not return a messagebox.

If one of the two groups is not filled in (blank) or returns an error it needs to show an "error msgbox"

The two groups of 3 cells are : (A39,A40,A41) and (E39,E40,E41)

This is currently in my formula:

``````If IsError(Range("E39, E40, E41")) Then MsgBox ("error msgbox"): Exit Sub
If Range("E39, E40, E41") = Blank Then MsgBox ("error msgbox"): Exit Sub
``````

How do I add A39,A40,A41 to this formula so that if either group 1 (A39,A40,A41) or
group 2 (E39,E40,E41) are filled in it does not return an "error msgbox"?

I've tried fiddling with it myself but it would return an "error msgbox" if all 6 cells weren't filled in.

I have tried looking at AND, OR statements but I can't quite get the function I want.

Here is a picture which may help Any Help would be greatly appreciated

• Can a single cell of a group be left blank, or do all cell's need to be blank? What would happen if only 5 cells were filled in (show message box or not)? Can you confirm that you want to see a message box only if both groups are NOT filled in (or on error)? – Dave Feb 12 '14 at 9:33
• Hey thanks for replying, If one of the 3 cells in a group is blank it needs to return an error msgbox (all 3 cells in a group need to have a value), it obviously also needs to return an error if both groups are blank/return an error – 12022014 Feb 12 '14 at 9:50
• Your last comment is confusing me slightly (sorry). What you're actually saying is (I think), if any cell (or cells) is an error or blank, then show the message box? – Dave Feb 12 '14 at 9:52
• Hey, so you (and everyone else) have more information I decided to make a simple image: s12.postimg.org/3y2y5xrv1/Geen_naam.png – 12022014 Feb 12 '14 at 10:18

You have some problems with your code. Firstly, `Range("E39, E40, E41")` does not refer to those three cells. You would need to separate them with commas outside of the quotes as so `Range("E39", "E40", "E41")`.

Secondly, in order to properly evaluate whether those cells are empty, you need to separate them in your `IF` statement, as so:

``````If Range("E39") = "" Or Range("E40") = "" Or Range("E41") = "" Then
'code here
End If
``````

Same goes for IsError:

``````If IsError(Range("E39")) Or IsError(Range("E40")) ...
``````

However, to group the logic then you need to think a different way, because this will throw the error when any of those cells are empty/contain an error. So what you might consider is adding two new variables which you set as `True` or `False` depending on whether the group is correctly filled in or not. Example:

``````Dim isFilled1 as Boolean = False
Dim isFilled2 as Boolean = False

If Not Range("E39") = "" Or Not Range("E40") = "" Or Not Range("E41") = "" Then
'All the cells in this range contain a value so set isFilled1 to True
isFilled1 = True
End If

If IsError(Range("E39")) Or IsError(Range("E40")) Or IsError(Range("E40")) Then
'The cells may contain values, but one or more evaluated as an error therefore set isFilled1 to False
isFilled1 = False
End If

If Not Range("A39") = "" Or Not Range("A40") = "" Or Not Range("A41") = "" Then
'All the cells in this range contain a value so set isFilled2 to True
isFilled2 = True
End If

If IsError(Range("A39")) Or IsError(Range("A40")) Or IsError(Range("A40")) Then
'The cells may contain values, but one or more evaluated as an error therefore set isFilled2 to False
isFilled2 = False
End If

'Now check if isFilled1 or isFilled2 are True

If isFilled1 = True or isFilled2 = True Then
'Hooray, one of the groups is properly filled
Else
'Neither group is properly filled; show msgbox
MsgBox("error msgbox")
End If
``````

You might need to tweak this code to suit your situation but this is how I would approach it.

• I edited it for my own needs but it doesn't seem to work. When only 2 of the 3 fields in a group are filled it does not return an error msgbox. I'm thining I need to change the Or statements to And? – 12022014 Feb 12 '14 at 11:09
• Changing the Or statements to And statements did indeed work. Thanks for the help! – 12022014 Feb 12 '14 at 11:14
• I'll put up the final code as an answer once I'm able to in about 5 hours – 12022014 Feb 12 '14 at 11:24
• Cool, glad i could help! – nagyben Feb 12 '14 at 22:23

I had to adjust it a little so it would work nicely with the excel file I was using.

With thanks to exantas (for the code) and Dave Rook (for editing my post), this is the final code:

``````Dim isFilled1 As Boolean
Dim isFilled2 As Boolean

If Not Range("E39") = "" And Not Range("E40") = "" And Not Range("E41") = "" Then
'Set isFilled1 To True if these cells don't return an empty cell
isFilled1 = True
End If
If IsEmpty(Range("E39")) Or IsEmpty(Range("E40")) Or IsEmpty(Range("E41")) Then
'If these cells don't have a value, set isFilled1 to False
isFilled1 = False
End If

If Not Range("A39") = "" And Not Range("A40") = "" And Not Range("A41") = "" Then
'Set isFilled1 To True if these cells don't return an empty cell
isFilled1 = True
End If
If IsEmpty(Range("A39")) Or IsEmpty(Range("A40")) Or IsEmpty(Range("A41")) Then
'If these cells don't have a value, set isFilled1 to False
isFilled1 = False
End If

If isFilled1 = True Or isFilled2 = True Then
'One of the groups is properly filled
Else
'Neither group is properly filled; show msgbox
MsgBox ("error msgbox"): Exit Sub
``````

I had to replace the formula in the cells E39,E40,E41,A39,A40,A41 with:

=IF(ISERROR(old_formula);"";(old_formula) so that it would display a blank/empty cell when an error is returned.