Determining if values in a column range are of same value.
 

Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some sort
of similar built in function?

Thanks!

Determining if values in a column range are of same value.
 

Hello Bing

One way:

If Evaluate("Min(B1:B1000)") = Evaluate("Max(B1:B1000)") Then
MsgBox "All the same"
End If

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Bing" skrev i en meddelelse
...
Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to
do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some
sort
of similar built in function?

Thanks!

Determining if values in a column range are of same value.
 

Most of the worksheet functions are available in VBA:

if application.Min(Range("Sheet1").Range("B1:B1000")) = _
application.Max(Range("Sheet1").Range("B1:B1000")) then

--
Regards,
Tom Ogilvy


"Bing" wrote in message
...
Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to
do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some
sort
of similar built in function?

Thanks!

Determining if values in a column range are of same value.
 

Another way

If application.countif(range("B1:B1000"),range("B1")) =
Range("B1:B1000").Cells.Count
' same

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bing" wrote in message
...
Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to
do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some
sort
of similar built in function?

Thanks!

Determining if values in a column range are of same value.
 

Thanks! you guys are great!

"Bob Phillips" wrote:

Another way

If application.countif(range("B1:B1000"),range("B1")) =
Range("B1:B1000").Cells.Count
' same

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bing" wrote in message
...
Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to
do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some
sort
of similar built in function?

Thanks!

Determining if values in a column range are of same value.
 

determine if values in a column range are the same.

If they are all numbers, perhaps another option:

If [STDEV(A1:B1000)] = 0 Then MsgBox "All same numbers"

--
Dana DeLouis
Win XP & Office 2003


"Bing" wrote in message
...
Hello,

Was wondering if there is a built in command or more efficent way to
determine if values in a column range are the same.

The simplest, but maybe not the most efficient way i can think of is to
do:

activesheet.Range("a1").Formula = "=Min('sheet1'!B1:B1000)"
activesheet.Range("a2").Formula = "=Max('sheet1'!B1:B1000)"
if activesheet.Range("a1").value = activesheet.Range("a2").value then
'values in column range are the same.
end if

Anyone think of a more efficient way of dong this? Does excel have some
sort
of similar built in function?

Thanks!