Can someone advise how to do this; so far have been unsuccessful, but I think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these 5)
that have something in them (it would be text), I want to check to ensure
that the content is all the same, ignoring those that are blank. Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1 have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

Are the blank cells empty or are the values produced produced by formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these 5)
that have something in them (it would be text), I want to check to ensure
that the content is all the same, ignoring those that are blank. Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these 5)
that have something in them (it would be text), I want to check to ensure
that the content is all the same, ignoring those that are blank. Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

What did you type in the cells?

Tom's code is looking for constants (not formulas) and text (not numbers).

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson

This may not be too slick but it worked

Dim i As Long
Dim j As Long
Dim Cell As Range
Dim nCell As Long
Dim CellRef() As String

On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Select
If Selection.Count 1 Then
ReDim CellRef(Selection.Count)
For Each Cell In Selection
nCell = nCell + 1
CellRef(nCell) = Cell.Address(False, False)
Next Cell
For i = 1 To nCell - 1
For j = i + 1 To nCell
If Range(CellRef(i)).Value < Range(CellRef(j)).Value Then
MsgBox "Contents don't match in these cells: " & CellRef(i) & " " &
CellRef(j)
End If
Next j
Next i
End If


"Paige" wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson

Yes, I did. Just copied your code and changed the cell addresses, so am not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson

It worked great for me.

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Yes, I did. Just copied your code and changed the cell addresses, so am
not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have
an
entry, they must match; if they do not match, I'm putting in a msgbox
that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put
into the
cells, it only returns the msgbox "All cells blank or contain
other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not
formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced
produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been
unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those
cells (of these
5)
that have something in them (it would be text), I want to
check to
ensure
that the content is all the same, ignoring those that are
blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be
blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson

Thanks, Tom, Dave and Charlie. I'm obviously having problems....am sure it
is user error. I'll work on this again tomorrow with all your suggestions;
hopefully am thinking clearer then - it's been a long day.

Really appreciate all your help!

"Charlie" wrote:

This may not be too slick but it worked

Dim i As Long
Dim j As Long
Dim Cell As Range
Dim nCell As Long
Dim CellRef() As String

On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlTextValues).Select
If Selection.Count 1 Then
ReDim CellRef(Selection.Count)
For Each Cell In Selection
nCell = nCell + 1
CellRef(nCell) = Cell.Address(False, False)
Next Cell
For i = 1 To nCell - 1
For j = i + 1 To nCell
If Range(CellRef(i)).Value < Range(CellRef(j)).Value Then
MsgBox "Contents don't match in these cells: " & CellRef(i) & " " &
CellRef(j)
End If
Next j
Next i
End If


"Paige" wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc

I'd check those addresses one more time.

I'd bet dollars to doughnuts (hmmmm, doughnuts!) that it's a slap the forehead
moment when you figure out the problem.

Paige wrote:

Yes, I did. Just copied your code and changed the cell addresses, so am not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi, Dave. I found out why it wasn't working. My spreadsheet is using merged
cells, with range names for each. It's ok with the range names themselves,
but not the fact that the cells are merged. Even if I reference them as
C1:D1,C3:D3, etc., it doesn't like that. Can this be fixed by changing
something in the code?

"Dave Peterson" wrote:

I'd check those addresses one more time.

I'd bet dollars to doughnuts (hmmmm, doughnuts!) that it's a slap the forehead
moment when you figure out the problem.

Paige wrote:

Yes, I did. Just copied your code and changed the cell addresses, so am not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I understand that the message may come back as different when they're all the
same, but that doesn't say why you get the first message to appear.

Option Explicit

Sub CheckCells()
Dim rng As Range, rng1 As Range, cell As Range
Dim sStr As String
Dim cCtr As Long
Set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
Set rng1 = rng.SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "All cells blank or contain other than text constants"
Else
sStr = rng1(1)
cCtr = 0
For Each cell In rng1
If cell.MergeArea.Cells(1).Address = cell.Address Then
cCtr = cCtr + 1
If cell.MergeArea.Cells(1).Value < sStr Then
MsgBox "Not all the same"
Exit Sub
End If
End If
Next
MsgBox rng1.Count & " (" & cCtr & ")" & " cells all contain " & sStr
End If
End Sub

But merged cells are strange beasts.

If this doesn't help, I'd unmerge those merged cells and see if there is any
formula lingering in the former mergearea.

And as a tip, I'd always refer to the "merged" cell by the top left cell in that
range.



Paige wrote:

Hi, Dave. I found out why it wasn't working. My spreadsheet is using merged
cells, with range names for each. It's ok with the range names themselves,
but not the fact that the cells are merged. Even if I reference them as
C1:D1,C3:D3, etc., it doesn't like that. Can this be fixed by changing
something in the code?

"Dave Peterson" wrote:

I'd check those addresses one more time.

I'd bet dollars to doughnuts (hmmmm, doughnuts!) that it's a slap the forehead
moment when you figure out the problem.

Paige wrote:

Yes, I did. Just copied your code and changed the cell addresses, so am not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks, Dave; will work on this further. Really appreciate all your help -
have a great Christmas!

"Dave Peterson" wrote:

I understand that the message may come back as different when they're all the
same, but that doesn't say why you get the first message to appear.

Option Explicit

Sub CheckCells()
Dim rng As Range, rng1 As Range, cell As Range
Dim sStr As String
Dim cCtr As Long
Set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
Set rng1 = rng.SpecialCells(xlConstants, xlTextValues)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "All cells blank or contain other than text constants"
Else
sStr = rng1(1)
cCtr = 0
For Each cell In rng1
If cell.MergeArea.Cells(1).Address = cell.Address Then
cCtr = cCtr + 1
If cell.MergeArea.Cells(1).Value < sStr Then
MsgBox "Not all the same"
Exit Sub
End If
End If
Next
MsgBox rng1.Count & " (" & cCtr & ")" & " cells all contain " & sStr
End If
End Sub

But merged cells are strange beasts.

If this doesn't help, I'd unmerge those merged cells and see if there is any
formula lingering in the former mergearea.

And as a tip, I'd always refer to the "merged" cell by the top left cell in that
range.



Paige wrote:

Hi, Dave. I found out why it wasn't working. My spreadsheet is using merged
cells, with range names for each. It's ok with the range names themselves,
but not the fact that the cells are merged. Even if I reference them as
C1:D1,C3:D3, etc., it doesn't like that. Can this be fixed by changing
something in the code?

"Dave Peterson" wrote:

I'd check those addresses one more time.

I'd bet dollars to doughnuts (hmmmm, doughnuts!) that it's a slap the forehead
moment when you figure out the problem.

Paige wrote:

Yes, I did. Just copied your code and changed the cell addresses, so am not
sure why it's not working. Sorry....

"Dave Peterson" wrote:

Did you change this line:
set rng = Range("A1,B9,F2,A10,M3")
to the cell addresses that you wanted?

Paige wrote:

The active sheet is correct. I'm entering 'weekly', or 'monthly' or
'quarterly' in the cells, or nothing at all. So for those that have an
entry, they must match; if they do not match, I'm putting in a msgbox that
will pop up.

"Dave Peterson" wrote:

And is the activesheet the correct one?

Paige wrote:

Thanks, Tom. I cannot get this to work. Regardless of what I put into the
cells, it only returns the msgbox "All cells blank or contain other than text
constants". Any advice?

"Tom Ogilvy" wrote:

So I will assume the filled cells are constants (and not formulas

Sub CheckCells()
Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String
set rng = Range("A1,B9,F2,A10,M3")
On Error Resume Next
set rng1 = rng.specialcells(xlconstants,xltextvalues)
On Error goto 0
if rng1 is nothing then
msgbox "All cells blank or contain other than text constants"
else
sStr = rng1(1)
for each cell in rng1
if cell.Value < sStr then
msgbox "Not all the same
exit sub
End if
Next
msgbox rng1.count & " cells all contain " & sStr
End if
end Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
Tom, the blank cells are empty.

"Tom Ogilvy" wrote:

Are the blank cells empty or are the values produced produced by
formulas?

=if(condition,"","horse")

type of formula.
--
Regards,
Tom Ogilvy

"Paige" wrote in message
...
Can someone advise how to do this; so far have been unsuccessful, but
I
think
it should be easy to do.

Example: There are 5 non-contiguous cells; for those cells (of these
5)
that have something in them (it would be text), I want to check to
ensure
that the content is all the same, ignoring those that are blank.
Note:
Sometimes all 5 cells could be blank, sometimes 4 could be blank and 1
have
an entry, or 3 blank and 2 with an entry, etc.

Thanks for any help you can provide....pc







--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson