Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf & CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I have
to loop through cells in G and cells in I and list them in the MsgBox, five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just plug
in that code, however, this time, there are two columns to consider -- if the
G cell is blank it needs to be displayed in the list, or if the G cell has
"No" or "N/A" then Column I has to be checked for a blank; if it is blank, it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell) Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf & CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider -- if
the
G cell is blank it needs to be displayed in the list, or if the G cell has
"No" or "N/A" then Column I has to be checked for a blank; if it is blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Hi Doug -

Thanks so much for your response. This is driving me crazy!!

Great solution, except I forgot to include in my message that there are
formulas in each of these cells. The text is blank unless another sheet has
been filled out that is linked to the cell. An example of a formula is:
=IF(G25="No",REF!F25,IF(G25="n/a",REF!F25,""))

So, what happens is the IsEmpty function doesn't really work. Also, I'm
having another problem: for some reason, the formula above throws a zero
into the I column and I don't know why. Therefore, also, it doesn't show up
as an error.
Do you have any idea what may be causing this? Do you have a workaround for
the fact that the IsEmpty function thinks a formula makes a cell not empty?

Again, many, many thanks!

--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell) Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf & CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider -- if
the
G cell is blank it needs to be displayed in the list, or if the G cell has
"No" or "N/A" then Column I has to be checked for a blank; if it is blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Hi again -

I did solve my problem with the following code and your code works
beautifully:
<snip
If (cell.Value = "No" Or cell.Value = "N/A") And Icell.Text = False Or
IsEmpty(Icell) Then
<snip
I do have another bigger problem, though. If the value in G is blank, I
have to return that also, so another scenario to be included is if G is blank
and column I is blank also, both column addresses have to appear in the
MsgBox.

Let me know your thoughts on the above.


--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell) Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf & CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider -- if
the
G cell is blank it needs to be displayed in the list, or if the G cell has
"No" or "N/A" then Column I has to be checked for a blank; if it is blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Sandy,

We can solve the formula in cell problem, by changing the IsEmpty line to:

If (cell.Value = "No" Or cell.Value = "N/A") And Icell = "" Then

However, you will still have the problem of the formula you mentioned
evaluating to 0. That's "normal" behavior. A simple example - if I put a
formula in A1 that says "=B1" and B1 has nothing in it then A1 will contain
0. The same is happening in your formula. You're saying if this condition
is met then I25 = REF!F25, which is empty, so the formula in I25 evaluates
to 0.

One question then is whether REF!25 would ever be expected to have an actual
value of zero. If not, then you could change the line of code to:

If (cell.Value = "No" Or cell.Value = "N/A") And Icell = 0 Then

But if REF!25 could be expected to contain a zero value, the next question
is whether that's any different, for the purposes of this macro, than a
blank. If the answer is no, then you could still use the formula directly
above. But if a blank in REF!25 is different than a zero, you need to
change your If formula. Perhaps:

=IF(OR(G25="No",G25="n/a"),IF(REF!$F$25<"",REF!$F$25,""),"")

This is all starting to seem a little to complicated though. Could you
avoid the macro altogether and just put the error checking in your formula,
e.g.:

=IF(AND(OR(G25="No",G25="n/a"),REF!$F$25 <""),REF!$F$25,"put something in"
& ADDRESS(ROW(),COLUMN(),,,"REF!"))

I hope I'm not just making it worse!

Doug

"Sandy" wrote in message
...
Hi Doug -

Thanks so much for your response. This is driving me crazy!!

Great solution, except I forgot to include in my message that there are
formulas in each of these cells. The text is blank unless another sheet
has
been filled out that is linked to the cell. An example of a formula is:
=IF(G25="No",REF!F25,IF(G25="n/a",REF!F25,""))

So, what happens is the IsEmpty function doesn't really work. Also, I'm
having another problem: for some reason, the formula above throws a zero
into the I column and I don't know why. Therefore, also, it doesn't show
up
as an error.
Do you have any idea what may be causing this? Do you have a workaround
for
the fact that the IsEmpty function thinks a formula makes a cell not
empty?

Again, many, many thanks!

--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell)
Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf &
CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider --
if
the
G cell is blank it needs to be displayed in the list, or if the G cell
has
"No" or "N/A" then Column I has to be checked for a blank; if it is
blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Sandy,

I'm sorry, I'm out of time until later today.

Have you looked at conditional formatting? I'm wondering if it wouldn't be
easier/more effective to highlight these various error situations on the
worksheet as they occur?

Good luck!

Doug

"Sandy" wrote in message
...
Hi again -

I did solve my problem with the following code and your code works
beautifully:
<snip
If (cell.Value = "No" Or cell.Value = "N/A") And Icell.Text = False Or
IsEmpty(Icell) Then
<snip
I do have another bigger problem, though. If the value in G is blank, I
have to return that also, so another scenario to be included is if G is
blank
and column I is blank also, both column addresses have to appear in the
MsgBox.

Let me know your thoughts on the above.


--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell)
Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf &
CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider --
if
the
G cell is blank it needs to be displayed in the list, or if the G cell
has
"No" or "N/A" then Column I has to be checked for a blank; if it is
blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") _
And Icell.Text = "False" Or IsEmpty(Icell) Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
Elseif cell = "" and Icell = "" then
If blank_cells Is Nothing Then
Set blank_cells = cell
Else
Set blank_cells = Union(blank_cells, cell)
End If
set blank_cells = Union(blank_cells,Icell)
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
Hi again -

I did solve my problem with the following code and your code works
beautifully:
<snip
If (cell.Value = "No" Or cell.Value = "N/A") And Icell.Text = False Or
IsEmpty(Icell) Then
<snip
I do have another bigger problem, though. If the value in G is blank, I
have to return that also, so another scenario to be included is if G is
blank
and column I is blank also, both column addresses have to appear in the
MsgBox.

Let me know your thoughts on the above.


--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell)
Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf &
CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0,
I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could
just
plug
in that code, however, this time, there are two columns to consider --
if
the
G cell is blank it needs to be displayed in the list, or if the G cell
has
"No" or "N/A" then Column I has to be checked for a blank; if it is
blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Doug -

Thanks to you, it's up and running today! I just did a little tweaking for
things I didn't mention in my post.

Thank you soooooo much for sticking with me on it!!
--
Sandy


"Doug Glancy" wrote:

Sandy,

I'm sorry, I'm out of time until later today.

Have you looked at conditional formatting? I'm wondering if it wouldn't be
easier/more effective to highlight these various error situations on the
worksheet as they occur?

Good luck!

Doug

"Sandy" wrote in message
...
Hi again -

I did solve my problem with the following code and your code works
beautifully:
<snip
If (cell.Value = "No" Or cell.Value = "N/A") And Icell.Text = False Or
IsEmpty(Icell) Then
<snip
I do have another bigger problem, though. If the value in G is blank, I
have to return that also, so another scenario to be included is if G is
blank
and column I is blank also, both column addresses have to appear in the
MsgBox.

Let me know your thoughts on the above.


--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell)
Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf &
CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0, I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could just
plug
in that code, however, this time, there are two columns to consider --
if
the
G cell is blank it needs to be displayed in the list, or if the G cell
has
"No" or "N/A" then Column I has to be checked for a blank; if it is
blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy

Tom -

Thanks so much for the extra tweaking! It's working beautifully!
--
Sandy


"Tom Ogilvy" wrote:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") _
And Icell.Text = "False" Or IsEmpty(Icell) Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
Elseif cell = "" and Icell = "" then
If blank_cells Is Nothing Then
Set blank_cells = cell
Else
Set blank_cells = Union(blank_cells, cell)
End If
set blank_cells = Union(blank_cells,Icell)
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
Hi again -

I did solve my problem with the following code and your code works
beautifully:
<snip
If (cell.Value = "No" Or cell.Value = "N/A") And Icell.Text = False Or
IsEmpty(Icell) Then
<snip
I do have another bigger problem, though. If the value in G is blank, I
have to return that also, so another scenario to be included is if G is
blank
and column I is blank also, both column addresses have to appear in the
MsgBox.

Let me know your thoughts on the above.


--
Sandy


"Doug Glancy" wrote:

Sandy,

I rewrote most of it. I hope this does what you want, or gives you some
help:

Sub test()

Dim cell As Range
Dim Grng As Range
Dim Icell As Range
Dim blank_cells As Range

With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
For Each cell In Grng
Set Icell = .Cells(cell.Row, "I")
If (cell.Value = "No" Or cell.Value = "N/A") And IsEmpty(Icell)
Then
If blank_cells Is Nothing Then
Set blank_cells = Icell
Else
Set blank_cells = Union(blank_cells, Icell)
End If
End If
Next cell
End With
MsgBox "Number of blanks: " & blank_cells.Count & vbCrLf & vbCrLf & _
"Blank cells(s): " & blank_cells.Address
End Sub

--
hth,

Doug
"Sandy" wrote in message
...
Dim c As Variant
Dim i As Variant
Dim numError As Integer
numError = 0
Dim Grng As Range
Dim iVar As Integer
Dim Arng As Variant
iVar = 0

Arng = Array("I9", "I15", "I19", "I22", "I25", "I39")
With ActiveSheet
Set Grng = .Range("G9, G15, G19, G22, G25, G39")
End With
For Each i In Grng.Cells
If i.Text = "No" Or i.Text = "N/A" Then
'Then check to see if indiv cell in I = empty
If Len(Trim(ActiveSheet.Range(Arng(iVar)))) <= 1 Then
numError = numError + 1
End If
iVar = iVar + 1
End If
Next i
MsgBox("Number of blanks: " & numError _
& vbCrLf & vbCrLf & "Blank cells(s): " & vbCrLf & vbCrLf &
CellAddresses



Cell Addresses is the part of the MsgBox I need. If numError is 0,
I
have
to loop through cells in G and cells in I and list them in the MsgBox,
five
cells per line with commas between each cell.

I did something similar to this a few days ago and thought I could
just
plug
in that code, however, this time, there are two columns to consider --
if
the
G cell is blank it needs to be displayed in the list, or if the G cell
has
"No" or "N/A" then Column I has to be checked for a blank; if it is
blank,
it
has to be listed too.

Any help will be greatly appreciated!!

--
Sandy