I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1, and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

John,

Your missing an End If

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End If 'MISSING
End Function

Dan E

"John Wirt" wrote in message ...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1, and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Unless your Personal.xls is an add-in, you need to explicitly
reference Personal.xls:

=Personal.xls!IsBetween(A1,B1,C1)

You also had an Endi If missing.


In article ,
"John Wirt" wrote:

I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1, and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Bob - that doesn't do what the OP's function does when A1=B1 or
A1=C1. One alternative:

=IF(OR(A1=B1,A1=C1),"",IF(AND(A1B1,A1<C1),"Yes"," No"))

In article ,
"Bob Phillips" wrote:

John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Function IsBetween(cell1, cell2, cell3) As String
IsBetween = _
IIf((cell1 cell2 And cell1 < cell3) Or _
(cell1 cell3 And cell1 < cell2), "Yes", "No")
End Function

--
Regards,
Tom Ogilvy



"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

How do I make Personal.xls an Add-in? Thanks.

John

"J.E. McGimpsey" wrote in message
...
Unless your Personal.xls is an add-in, you need to explicitly
reference Personal.xls:

=Personal.xls!IsBetween(A1,B1,C1)

You also had an Endi If missing.


In article ,
"John Wirt" wrote:

I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Because I get tired of typing this long formula in and changing the cell
address many times. Block copy won't work in my sitiuation.
"Bob Phillips" wrote in message
...
John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Oops - dropped the rest of your function:

=IF(OR(A1=B1,A1=C1),"",IF(OR(AND(A1B1,A1<C1),AND( A1<B1,A1C1)),
"Yes","No"))


In article
,
"J.E. McGimpsey" wrote:

Bob - that doesn't do what the OP's function does when A1=B1 or
A1=C1. One alternative:

=IF(OR(A1=B1,A1=C1),"",IF(AND(A1B1,A1<C1),"Yes"," No"))

select it in the project explorer and select ThisWorkbook. Then go to the
properties window and select the isaddin property and set it to true.

--
Regards,
Tom Ogilvy

"John Wirt" wrote in message
...
How do I make Personal.xls an Add-in? Thanks.

John

"J.E. McGimpsey" wrote in message
...
Unless your Personal.xls is an add-in, you need to explicitly
reference Personal.xls:

=Personal.xls!IsBetween(A1,B1,C1)

You also had an Endi If missing.


In article ,
"John Wirt" wrote:

I want to write a VBA function that compares the numeric values in
cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1,
B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

JE,

You're right of course, but I find it difficult to believe that the OP wants
to show a blank if A1 is on one of the boundaries. I may be (probably am?)
wrong, but I would venture that it is a case that he didn't think of all the
possibilities as you did.

Regards

Bob

"J.E. McGimpsey" wrote in message
...
Bob - that doesn't do what the OP's function does when A1=B1 or
A1=C1. One alternative:

=IF(OR(A1=B1,A1=C1),"",IF(AND(A1B1,A1<C1),"Yes"," No"))

In article ,
"Bob Phillips" wrote:

John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in
cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1,
B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

It works, lads. Thank you.

Will be used a lot in checking statistical tables for the National Center
for Education Statistics.

John


"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

You're right, I didn't think of the boundaries. I wnat the function to show
"Err? if any of the entries are equal (exactly equal), so I've added that to
function.

John


"Bob Phillips" wrote in message
...
JE,

You're right of course, but I find it difficult to believe that the OP
wants
to show a blank if A1 is on one of the boundaries. I may be (probably am?)
wrong, but I would venture that it is a case that he didn't think of all
the
possibilities as you did.

Regards

Bob

"J.E. McGimpsey" wrote in message
...
Bob - that doesn't do what the OP's function does when A1=B1 or
A1=C1. One alternative:

=IF(OR(A1=B1,A1=C1),"",IF(AND(A1B1,A1<C1),"Yes"," No"))

In article ,
"Bob Phillips" wrote:

John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in
cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1,
B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

Yes, this is a bit slicker.

John


"Tom Ogilvy" wrote in message
...
Function IsBetween(cell1, cell2, cell3) As String
IsBetween = _
IIf((cell1 cell2 And cell1 < cell3) Or _
(cell1 cell3 And cell1 < cell2), "Yes", "No")
End Function

--
Regards,
Tom Ogilvy



"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

John,

Why do you need to keep typing it? Copy/paste will update the references to
relative to the target cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
Because I get tired of typing this long formula in and changing the cell
address many times. Block copy won't work in my sitiuation.
"Bob Phillips" wrote in message
...
John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in
cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1,
B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt

I started out using this function and found that I was spending most of my
time getting just the right columns in the formula every place I need it.
The columns are not always adjacent to each other.

Sometimes a VBA function is better.

John

"Bob Phillips" wrote in message
...
John,

Why use VBA when you can do it with a worksheet function

=IF(OR(AND(A1B1,A1<C1),AND(A1<B1,A1C1)),"Yes","N o")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I want to write a VBA function that compares the numeric values in cell1
against the value is cell2 and cell 3, and reports "Yes" if cell1 is
between
cell2 and cell 3, and otherwise "No." Here is a cut & paste of the
function
I;ve written in PERSONAL.XLS (see below).

WHen I enter =IsBetween(A1, B1,C1) in D1 with numeric values in A1, B1,
and
C1 I get a #NAME error in D1. How come?

Function IsBetween(cell1, cell2, cell3) As String
If cell1 cell2 Then
If cell1 < cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
ElseIf cell1 < cell2 Then
If cell1 cell3 Then
IsBetween = "Yes"
Else
IsBetween = "No"
End If
End Function


Thanks.

John Wirt