Compound IF statement

[Guest]

I am attempting to code a UDF for the first time. The small spreadsheet I've
set up is in sheet 1 ("Database") and just two columns relate to my
question. Col 1 is populated with either "BA" or "V" and Col 2 with "AD" or
"DC". If BA = DC, then I need to sum those enties into a cell on a report
section on sheet 2 ("Report"). Thus, I an ignoring "V" and "AD" entries.

How do I code the UDF? How do I get it into the spreadsheet? I have been
reading about VBA, know I need a UDF and need a Sub routine to call the UDF.
But I've never done either of those events.

I am using Excel 2000.

Any help at all, with either of these questions, would be so much
appreciated!

Thank you.

 

[Otto Moehrbach]

Di
It's hard to follow what you are saying. You say "If BA = DC" but that
can never be. Do you mean when Col 1 has BA and the corresponding cell in
Col 2 has DC? Then you say you want to "sum those entries". There is
nothing to sum. Do you mean that you want to count the number of rows that
have both BA in Col 1 and DC in Col 2?
For what you want, a count in some cell, you would not need a sub
routine to call the UDF. You would simply put the UDF name and whatever
argument(s) you have, in that cell. Something like:
=MyUDF(TheArgument).
Please post back and clarify what you want to do and you will get plenty
of help. HTH Otto

 

[Guest]

Otto,

Thank you so much for reading my plea and I apologize for being vague. I'll
try again and provide more detail. And you did interpret the situation
correctly.

The project is an Excel spreadsheet with a linked report which will be
printed at the end of the month. There are, of course, more than 2 columns,
in the spreadsheet, but just 2 are relevant here.

Col 1 can have 2 entries: BA (for Baker Act) or V (for Voluntary)
Col 2 can have 2 entries: DC (for Discharge) or Ad (for Admit)

If Col 1 has BA and if the same cell in Col 2 has DC, then the Baker Act was
rescinded and I want to sum those occurrences to show the total number
rescinded for the month.

I hope I've expressed the situation more completely. My only acquaintance
with programming was a couple of introductory courses back in the 70s. So
this project was a bit over my head, and when my reading wasn't getting the
job done, I decided to turn to this site. Believe me, I am truly grateful
for any help you can give me.

Di

 

[Otto Moehrbach]

Di
Here are two bits of code for you to play with and pick what's most
suitable.
The first one is a macro and it returns a message box with the answer you
want. I needed a range of your data for this macro and I chose A1:B11. You
can change this as you wish. Note that the two lines with "AutoFilter" in
them specify to look at the first two columns (fields) of the range you
specify so you will have to change that also. You can also add code to the
beginning of the macro to get the range if you wish. Note that the range
has to include the header row.
The second bit of code is a UDF. You have to specify the range over
which you want the code to look. In the cell in which you want the answer
you would enter, without the quotes:
"=TheCount(A1:B11)"
Please post back if you need more. HTH Otto

Sub CountsBACD()
Dim TheRng As Range
Dim CountBACD As Long
Application.ScreenUpdating = False
Set TheRng = Range("A1:B11")
TheRng.AutoFilter Field:=1, Criteria1:="BA"
TheRng.AutoFilter Field:=2, Criteria1:="CD"
CountBACD = (TheRng.SpecialCells(xlCellTypeVisible).Count - 2) / 2
TheRng.AutoFilter
MsgBox CountBACD
End Sub

Function TheCount(TheRng As Range) As Long
Dim i As Range
Dim c As Long
c = 0
For Each i In TheRng
If i.Value = "BA" And i.Offset(, 1).Value = "CD" Then
c = c + 1
End If
Next i
TheCount = c
End Function

 

[Guest]

Otto,

You have given me 2 situations and both look like exactly what I needed. I
was thinking of a UDF, but I like the Sub Counts. I will try them both as
they will also be great learning opportunities for me. I devote Sundays to
this project, so I will use the time inbetween to see if I can try them on my
Mac at home. Else I might have to wait to get into the office with the PC.
I was envisioning something a little different. No wonder I was having so
much difficulty.

Thank you, Otto. You have been a marvelous resource and I am very grateful.

Di

 

[Otto Moehrbach]

Glad I could help. Otto

Otto,

You have given me 2 situations and both look like exactly what I needed.
I
was thinking of a UDF, but I like the Sub Counts. I will try them both as
they will also be great learning opportunities for me. I devote Sundays
to
this project, so I will use the time inbetween to see if I can try them on
my
Mac at home. Else I might have to wait to get into the office with the
PC.
I was envisioning something a little different. No wonder I was having so
much difficulty.

Thank you, Otto. You have been a marvelous resource and I am very
grateful.

Di

 

[Guest]

Otto,

I'm trying the Function on my Mac and have a couple of problems. (1) I may
not have entered the first 2 lines correctly. I took your statement:
Function TheCount(TheRng As Range) As Long and did this:

Function TheCount()
Dim TheRng As Range
Dim i As Range

All else follows exactly what you gave me.

The error I get is "Runtime error 424. Object Required" I tried to
research that error but it didn't help and I know I'm missing something,
perhaps the fiew lines of code are not correct.

I'm grateful for your input. And your If statement was precisely what I
needed. The code is a great bonus. Thank You.

 

[Otto Moehrbach]

Di
A function needs an argument. In other words, a function needs to be
told what to work on.
My statement:
Function TheCount(TheRng As Range) As Long
declares TheRng as the argument and declares it as a Range. The "As Long"
declares TheCount as Long. You don't have to declare any variable and you
don't have to state the data type, but you must have TheRng in parentheses
after "TheCount"). That way, when you type (=TheCount(A1:B11) in a cell,
the function will know what TheRng is.
What you have done is to mix the function construct with the procedure
(macro) construct. The statement:
Sub TheCount()
is fine for a macro but the statement:
Function TheCount()
is not. HTH Otto

 

[Guest]

Ok, Otto. Now I think I understand. This 70+ brain is attempting to learn
something for the first time and, as is obvious, I'm struggling. However,
I'm not ready to give up just yet. And I do believe that now I have filled
in all the blanks in my understanding of this situation. So for that, and
your patience, I say again, Thank You Very Much. Di