0

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

enter image description here

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
1

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.

| improve this answer | |
  • 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
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.