 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007Compound IF statement
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. 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 >I am attempting to code a UDF for the first time. The small spreadsheet >I've [quoted text clipped - 16 lines] > > Thank you. 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 > Di > It's hard to follow what you are saying. You say "If BA = DC" but that [quoted text clipped - 28 lines] > > > > Thank you. 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 > Otto, > [quoted text clipped - 64 lines] >> > >> > Thank you. 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 > Di > Here are two bits of code for you to play with and pick what's most [quoted text clipped - 104 lines] > >> > > >> > Thank you. Glad I could help. Otto > Otto, > [quoted text clipped - 136 lines] >> >> > >> >> > Thank you. 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. > Glad I could help. Otto > > Otto, [quoted text clipped - 137 lines] > >> >> > > >> >> > Thank you. 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 > Otto, > [quoted text clipped - 186 lines] >> >> >> > >> >> >> > Thank you. 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 > Di > A function needs an argument. In other words, a function needs to be [quoted text clipped - 202 lines] > >> >> >> > > >> >> >> > Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.