I want to check the completeness of a report. If one of the cell in Range
"D14:F14" is not blank then one of the cell in "G14:H14" should also be not
blank, vice versa. Below is my code and it is not working. Thanks in
advance.

Set SThree(0, 0) = .Range("D14:F14")
Set SThree(0, 1) = .Range("G14:H14")

For Each Cell In SThree(0, 0)
If Cell <> "" Then
For Each Cells In SThree(0, 1)
If Cells = "" Then
MsgBox "Not Complete"
Cancel = True
Exit Sub
Else
Cancel = True
Exit Sub
End If

Next
Next
--
Thanks!

I don't know if <> is the right test since there are different numbers of
cells - but maybe this will give you ideas:

if application.CountA(.Range("D14:F14")) <> Application.CountA( _
.Range("G1414")) then
MsgBox "Not Complete"
Cancel = True
Exit Sub
end if

--
Regards,
Tom Ogilvy






"Daviv" wrote:

> I want to check the completeness of a report. If one of the cell in Range
> "D14:F14" is not blank then one of the cell in "G14:H14" should also be not
> blank, vice versa. Below is my code and it is not working. Thanks in
> advance.
>
> Set SThree(0, 0) = .Range("D14:F14")
> Set SThree(0, 1) = .Range("G14:H14")
>
> For Each Cell In SThree(0, 0)
> If Cell <> "" Then
> For Each Cells In SThree(0, 1)
> If Cells = "" Then
> MsgBox "Not Complete"
> Cancel = True
> Exit Sub
> Else
> Cancel = True
> Exit Sub
> End If
>
> Next
> Next
> --
> Thanks!

Hi Daviv -

Here's some replacement code. Adapt as necessary:

a = Application.CountA(Range("D14:F14"))
b = Application.CountA(Range("G14:H14"))
If (a > 0 And b = 0) Or (b > 0 And a = 0) Then
MsgBox "Not Complete"
Cancel = True
Exit Sub
Else
MsgBox "aok" '<<====Delete after testing
Cancel = True
Exit Sub
End If

--
Jay


"Daviv" wrote:

> I want to check the completeness of a report. If one of the cell in Range
> "D14:F14" is not blank then one of the cell in "G14:H14" should also be not
> blank, vice versa. Below is my code and it is not working. Thanks in
> advance.
>
> Set SThree(0, 0) = .Range("D14:F14")
> Set SThree(0, 1) = .Range("G14:H14")
>
> For Each Cell In SThree(0, 0)
> If Cell <> "" Then
> For Each Cells In SThree(0, 1)
> If Cells = "" Then
> MsgBox "Not Complete"
> Cancel = True
> Exit Sub
> Else
> Cancel = True
> Exit Sub
> End If
>
> Next
> Next
> --
> Thanks!

Thanks both. It works beautifully.
--
Thanks!


"Jay" wrote:

> Hi Daviv -
>
> Here's some replacement code. Adapt as necessary:
>
> a = Application.CountA(Range("D14:F14"))
> b = Application.CountA(Range("G14:H14"))
> If (a > 0 And b = 0) Or (b > 0 And a = 0) Then
> MsgBox "Not Complete"
> Cancel = True
> Exit Sub
> Else
> MsgBox "aok" '<<====Delete after testing
> Cancel = True
> Exit Sub
> End If
>
> --
> Jay
>
>
> "Daviv" wrote:
>
> > I want to check the completeness of a report. If one of the cell in Range
> > "D14:F14" is not blank then one of the cell in "G14:H14" should also be not
> > blank, vice versa. Below is my code and it is not working. Thanks in
> > advance.
> >
> > Set SThree(0, 0) = .Range("D14:F14")
> > Set SThree(0, 1) = .Range("G14:H14")
> >
> > For Each Cell In SThree(0, 0)
> > If Cell <> "" Then
> > For Each Cells In SThree(0, 1)
> > If Cells = "" Then
> > MsgBox "Not Complete"
> > Cancel = True
> > Exit Sub
> > Else
> > Cancel = True
> > Exit Sub
> > End If
> >
> > Next
> > Next
> > --
> > Thanks!

Thanks for both. It works beautifully.
--
Thanks!


"Daviv" wrote:

> I want to check the completeness of a report. If one of the cell in Range
> "D14:F14" is not blank then one of the cell in "G14:H14" should also be not
> blank, vice versa. Below is my code and it is not working. Thanks in
> advance.
>
> Set SThree(0, 0) = .Range("D14:F14")
> Set SThree(0, 1) = .Range("G14:H14")
>
> For Each Cell In SThree(0, 0)
> If Cell <> "" Then
> For Each Cells In SThree(0, 1)
> If Cells = "" Then
> MsgBox "Not Complete"
> Cancel = True
> Exit Sub
> Else
> Cancel = True
> Exit Sub
> End If
>
> Next
> Next
> --
> Thanks!