Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses returned,
which would make the message box extend completely across the screen. sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Sub EF()
Dim sStr As String
Dim rng As Range
Dim rng1 as Range
Dim j As Long, i As Long
j = 0
Set rng = Range("A1").Resize(8, 8)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng1 Is Nothing Then
sStr = rng1.Address(0, 0)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = Chr(10)
j = 0
End If
End If
Next

MsgBox ("The blank cells a " & vbCrLf & _
vbCrLf & sStr & vbCrLf & vbCrLf & _
"You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

End If

End Sub

--
Regards,
Tom Ogilvy



"Sandy" wrote in message
...
Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Sandy,

This macro assumes your processing a selection, which is probably not the
case so adjust the range type as necessary:

Sub test()

Dim rCell As Range
Dim sStr As String
Dim lBlanksCount As Long
Dim sSeparator As String

lBlanksCount = 0

For Each rCell In Selection
If IsEmpty(rCell.Value) Then
lBlanksCount = lBlanksCount + 1
If lBlanksCount Mod 3 = 0 Then
sSeparator = vbCrLf
Else
sSeparator = " "
End If
sStr = sStr & " " & rCell.Address & sSeparator
End If
Next rCell

MsgBox ("The blank cells a" & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

End Sub

hth,

Doug

"Sandy" wrote in message
...
Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Sandy,

I noticed that mine prints a message even if there are no blanks and you got
a perfect answer from Tom anyways, but just for the record, this would fix
it:

If sStr < "" Then
MsgBox ("The blank cells a" & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
End If

hth,

"Doug Glancy" wrote in message
...
Sandy,

This macro assumes your processing a selection, which is probably not the
case so adjust the range type as necessary:

Sub test()

Dim rCell As Range
Dim sStr As String
Dim lBlanksCount As Long
Dim sSeparator As String

lBlanksCount = 0

For Each rCell In Selection
If IsEmpty(rCell.Value) Then
lBlanksCount = lBlanksCount + 1
If lBlanksCount Mod 3 = 0 Then
sSeparator = vbCrLf
Else
sSeparator = " "
End If
sStr = sStr & " " & rCell.Address & sSeparator
End If
Next rCell

MsgBox ("The blank cells a" & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

End Sub

hth,

Doug

"Sandy" wrote in message
...
Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Thanks so much to both of you!

For some reason, I wasn't able to get Doug's to work, but I was probably
putting it in the wrong part of my code

Tom - I plugged some of your code into my code and it does work -- except
for formatting. I am getting a result returned as follows:

The blank cells a
D13, D14
D15, D16, D17
D18, D19, D20

Note the D13 and D14 -- there should be three in that row; not 2, and also
the D13 is out of line with the D15 and D18.

Here is my code. What am I doing wrong?

'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & ", " & rCell.Address(0, 0)
End If
Next rCell

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
sStr = Mid(sStr, 3)
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If

--
Sandy


"Sandy" wrote:

Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses returned,
which would make the message box extend completely across the screen. sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Sub CCC()
'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If
End Sub

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
Thanks so much to both of you!

For some reason, I wasn't able to get Doug's to work, but I was probably
putting it in the wrong part of my code

Tom - I plugged some of your code into my code and it does work -- except
for formatting. I am getting a result returned as follows:

The blank cells a
D13, D14
D15, D16, D17
D18, D19, D20

Note the D13 and D14 -- there should be three in that row; not 2, and also
the D13 is out of line with the D15 and D18.

Here is my code. What am I doing wrong?

'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & ", " & rCell.Address(0, 0)
End If
Next rCell

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
sStr = Mid(sStr, 3)
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If

--
Sandy


"Sandy" wrote:

Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a
vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy

Tom -

Great example of brilliance at its best!! That works beautifully!

Thank you so much.
--
Sandy


"Tom Ogilvy" wrote:

Sub CCC()
'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & rCell.Address(0, 0) & ","
End If
Next rCell
sStr = Left(sStr, Len(sStr) - 1)

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If
End Sub

--
Regards,
Tom Ogilvy

"Sandy" wrote in message
...
Thanks so much to both of you!

For some reason, I wasn't able to get Doug's to work, but I was probably
putting it in the wrong part of my code

Tom - I plugged some of your code into my code and it does work -- except
for formatting. I am getting a result returned as follows:

The blank cells a
D13, D14
D15, D16, D17
D18, D19, D20

Note the D13 and D14 -- there should be three in that row; not 2, and also
the D13 is out of line with the D15 and D18.

Here is my code. What am I doing wrong?

'Count the empty cells
Dim numBlanks As Integer
numBlanks = 0
For Each c In Range("D13:D24")
If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

Dim sStr As String
Dim rRng As Range
Dim rCell As Range
Dim j As Long, i As Long
j = 0
With ActiveSheet
Set rRng = .Range("D13:D24")
End With
sStr = ""
For Each rCell In rRng.Cells
If IsEmpty(rCell) Then
sStr = sStr & ", " & rCell.Address(0, 0)
End If
Next rCell

For i = 1 To Len(sStr)
If Mid(sStr, i, 1) = "," Then
j = j + 1
If j = 3 Then
Mid(sStr, i, 1) = vbCrLf
j = 0
End If
End If
Next

'If no blanks go to Sheet2 or return error message
If Len(sStr) 0 Then
sStr = Mid(sStr, 3)
MsgBox ("There are " & numBlanks & " empty cells on this worksheet." _
& vbCrLf & vbCrLf & "The blank cells a " & vbCrLf & vbCrLf & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]
Else
Sheet2.Activate
End If

--
Sandy


"Sandy" wrote:

Hello -

I have a msgbox that returns a concatenation of cell addresses that are
blank. My problem is there could be as many as 64 cell addresses
returned,
which would make the message box extend completely across the screen.
sStr
is the cell address. Here's the code:

MsgBox ("The blank cells a " & sStr _
& vbCrLf & vbCrLf & "You must fill them in before proceeding!"), _
[vbOKOnly], ["Blank Cells!"]

Is there any way I can get it to display three sStr and then do a
vbCrLf,
then another three, etc.?

Any help will be greatly appreciated!
--
Sandy