Helpneeded in writing special function, "IsBetween"

J

John Wirt

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
 
D

Dan E

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
 
B

Bob Phillips

John,

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

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

--

HTH

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

J.E. McGimpsey

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.
 
J

J.E. McGimpsey

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(A1>B1,A1<C1),"Yes","No"))
 
T

Tom Ogilvy

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

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.
 
J

J.E. McGimpsey

Oops - dropped the rest of your function:

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

Tom Ogilvy

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.
 
B

Bob Phillips

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

John Wirt

It works, lads. Thank you.

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

John
 
J

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
 
J

John Wirt

Yes, this is a bit slicker.

John


Tom Ogilvy said:
Function IsBetween(cell1, cell2, cell3) As String
IsBetween = _
IIf((cell1 > cell2 And cell1 < cell3) Or _
(cell1 > cell3 And cell1 < cell2), "Yes", "No")
End Function
 
B

Bob Phillips

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)
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top