Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
Hello,
I'm trying to write a statement macro and one of the cells is a comments section where it evaluates other cells in the row. However, I've exceeded the number of IF statements by one: =IF(A3="","", IF(COUNTIF(S$3:S$20000,A3)1, "Duplicate or secondary invoice in GP", IF(AND(ISERROR(H3),J3="Yes"), "Scheduled to pay/apply", IF(J3="Yes","Paid/Applied", IF(K3="Yes", "Dropship import error", IF(L3="Yes", "Duplicate or secondary invoice in VNet", IF(ISTEXT(E3), "Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Goods", "Open")))))))) Something a little more readable: If A3 = "" Then Print "" Elseif A3 is found in S3:S20000 more than once Then Print "Duplicate or Secondary Invoice" Elseif H3 is an Error AND J3 = "Yes" Then Print "Scheduled to pay/apply" Elseif J3 = "Yes" Then Print "Paid/Applied" Elseif K3 = "Yes" Then Print "Dropship import error" Elseif L3 = "Yes" Then Print "Duplicate or secondary invoice in VNet" Elseif E3 ISTEXT Then Print "Open - Dropship" Elseif E3 ISNUMBER Then Print "Open - Owned Goods" Else Print "Open" End If At first I tried to use two Named Formulas to bypass my way around the limitation but I kept getting a #VALUE error and I couldn't figure out why. I don't have any VBA coding experience so I'm wondering if someone can translate the above into a function? If someone sees a way to make the original formula more condensed that'd be great too. Thanks in advance... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
You could always spread your formula across two cells. Put the first five IFs
in your destination cell. For the final FALSE result, put in a reference to the second cell. In the second cell put a formula with the other IFs. The second will always return something, but it will only be used if the IFs in the first cell are all FALSE. Or, here is an equivalent custom function: Public Function ShtComment(Rng As Range) As String Dim WS As Worksheet On Error GoTo SCerr1 'If Rng contains more than one cell, return ERROR. If Rng.Count 1 Then ShtComment$ = "ERROR" Exit Function End If Set WS = Rng.Parent 'If A3 = "" Then If Len(Range("A" & Rng.Row).Value) = 0 Then ShtComment$ = vbNullString 'Elseif A3 is found in S3:S20000 more than once Then ElseIf Application.WorksheetFunction.CountIf(WS.Range("S$ 3:S$20000"), Range("A" & Rng.Row).Value) 1 Then ShtComment$ = "Duplicate or Secondary Invoice" 'Elseif H3 is an Error AND J3 = "Yes" Then ElseIf IsError(Range("H" & Rng.Row).Value) And LCase(Range("J" & Rng.Row).Value) = "yes" Then ShtComment$ = "Scheduled to pay/apply" 'ElseIf J3 = "Yes" Then ElseIf LCase(Range("J" & Rng.Row).Value) = "yes" Then ShtComment$ = "Paid/Applied" 'ElseIf K3 = "Yes" Then ElseIf LCase(Range("K" & Rng.Row).Value) = "yes" Then ShtComment$ = "Dropship import error" 'ElseIf L3 = "Yes" Then ElseIf LCase(Range("L" & Rng.Row).Value) = "yes" Then ShtComment$ = "Duplicate or secondary invoice in VNet" 'Elseif E3 ISTEXT Then ElseIf Application.WorksheetFunction.IsText(Range("E" & Rng.Row).Value) Then ShtComment$ = "Open - Dropship" 'Elseif E3 ISNUMBER Then ElseIf Application.WorksheetFunction.IsNumber(Range("E" & Rng.Row).Value) Then ShtComment$ = "Open - Owned Goods" Else ShtComment$ = "Open" End If Cleanup1: Set WS = Nothing Exit Function SCerr1: ShtComment$ = "ERROR" GoTo Cleanup1 End Function Please note: I haven't tested this function. Hope this helps, Hutch "gardenhead" wrote: Hello, I'm trying to write a statement macro and one of the cells is a comments section where it evaluates other cells in the row. However, I've exceeded the number of IF statements by one: =IF(A3="","", IF(COUNTIF(S$3:S$20000,A3)1, "Duplicate or secondary invoice in GP", IF(AND(ISERROR(H3),J3="Yes"), "Scheduled to pay/apply", IF(J3="Yes","Paid/Applied", IF(K3="Yes", "Dropship import error", IF(L3="Yes", "Duplicate or secondary invoice in VNet", IF(ISTEXT(E3), "Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Goods", "Open")))))))) Something a little more readable: If A3 = "" Then Print "" Elseif A3 is found in S3:S20000 more than once Then Print "Duplicate or Secondary Invoice" Elseif H3 is an Error AND J3 = "Yes" Then Print "Scheduled to pay/apply" Elseif J3 = "Yes" Then Print "Paid/Applied" Elseif K3 = "Yes" Then Print "Dropship import error" Elseif L3 = "Yes" Then Print "Duplicate or secondary invoice in VNet" Elseif E3 ISTEXT Then Print "Open - Dropship" Elseif E3 ISNUMBER Then Print "Open - Owned Goods" Else Print "Open" End If At first I tried to use two Named Formulas to bypass my way around the limitation but I kept getting a #VALUE error and I couldn't figure out why. I don't have any VBA coding experience so I'm wondering if someone can translate the above into a function? If someone sees a way to make the original formula more condensed that'd be great too. Thanks in advance... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
First off, thanks for the reply.
I'm trying your function out but I don't think I'm using it correctly. It wasn't clear to me if I should have something as it's input. In cell G3 I tried "=ShtComment", "=ShtComment()" and "=Sht(Comment(A3)" and a bunch of other cell ranges and they all gave me a #NAME error. The first suggestion is definitely doable (and I will be using that if the above doesn't work out) but it'd be nice to not have to enter another column as the spreadsheet's already 36 columns wide :D |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
You need to copy the function into a VBA code module (not a sheet or ThisWorkbook module) for it to be visible to Excel. It takes a single cell as an argument: =Sht(Comment(A3) if called from A3. But, why would you call it from A3? You are testing A3 in the function to see if it is empty. If it has a formula, it is not empty. Regards, Hutch "gardenhead" wrote: First off, thanks for the reply. I'm trying your function out but I don't think I'm using it correctly. It wasn't clear to me if I should have something as it's input. In cell G3 I tried "=ShtComment", "=ShtComment()" and "=Sht(Comment(A3)" and a bunch of other cell ranges and they all gave me a #NAME error. The first suggestion is definitely doable (and I will be using that if the above doesn't work out) but it'd be nice to not have to enter another column as the spreadsheet's already 36 columns wide :D |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
ARGGHH! Typo time. The function call should be:
=ShtComment(A3) Regarding splitting the formula across cells in 2 columns: once you are satisfied you are getting the expected results, you could hide the column with the second cell. "Tom Hutchins" wrote: You need to copy the function into a VBA code module (not a sheet or ThisWorkbook module) for it to be visible to Excel. It takes a single cell as an argument: =Sht(Comment(A3) if called from A3. But, why would you call it from A3? You are testing A3 in the function to see if it is empty. If it has a formula, it is not empty. Regards, Hutch "gardenhead" wrote: First off, thanks for the reply. I'm trying your function out but I don't think I'm using it correctly. It wasn't clear to me if I should have something as it's input. In cell G3 I tried "=ShtComment", "=ShtComment()" and "=Sht(Comment(A3)" and a bunch of other cell ranges and they all gave me a #NAME error. The first suggestion is definitely doable (and I will be using that if the above doesn't work out) but it'd be nice to not have to enter another column as the spreadsheet's already 36 columns wide :D |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
Hi Tom,
I caught the typo before, no worries. I'm still unable to get the function to work however. Column G is where I want the comments to be made so I enter in =ShtComment(G3) and I get the #NAME error. I had the function listed underneath my macro module so I believe it was in the right place to begin with. Good news is that I came up with another solution. Bad news, of course, is that it poses a new problem. I decided to do away with the first IF statement that checked the A3 cell for a blank and combine it with the next one with an AND function so it currently looks like this: =IF(AND(NOT(ISBLANK(A3)),COUNTIF(S$3:S$20000,A3)1 ), "Duplicate or secondary invoice in GP", IF(AND(ISERROR(H3),J3="Yes"),"Scheduled to pay/apply",IF(J3="Yes","Paid/Applied",IF(K3="Yes","Dropship import error", IF(L3="Yes", "Duplicate or secondary invoice in VNet", IF(ISTEXT(E3),"Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Good", "Open"))))))) Works good so far, but now I just need to figure out how to highlight and delete all the blank cells in the E column for it to work perfectly. There seems to be a lot of advice already in eliminating blank cells so that shouldn't be too much trouble. Thanks again for the help, I'm planning a crash course in VBA and I hope to come back to that function and make it work. Tom Hutchins wrote: ARGGHH! Typo time. The function call should be: =ShtComment(A3) Regarding splitting the formula across cells in 2 columns: once you are satisfied you are getting the expected results, you could hide the column with the second cell. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
When I call the function from Excel, I don't get an error. Adding data to the
various columns, it returns the values we would expect. Are you sure you copied it into a VBA module (Insert Module in the Visual Basic Editor) in the same workbook? It must have the Public keyword, also. You should be able to find (and use) it by selecting Function from the Insert menu in Excel, then selecting the category User Defined. Your revised formula doesn't return "" if A3 is empty. Regards, Hutch "gardenhead" wrote: Hi Tom, I caught the typo before, no worries. I'm still unable to get the function to work however. Column G is where I want the comments to be made so I enter in =ShtComment(G3) and I get the #NAME error. I had the function listed underneath my macro module so I believe it was in the right place to begin with. Good news is that I came up with another solution. Bad news, of course, is that it poses a new problem. I decided to do away with the first IF statement that checked the A3 cell for a blank and combine it with the next one with an AND function so it currently looks like this: =IF(AND(NOT(ISBLANK(A3)),COUNTIF(S$3:S$20000,A3)1 ), "Duplicate or secondary invoice in GP", IF(AND(ISERROR(H3),J3="Yes"),"Scheduled to pay/apply",IF(J3="Yes","Paid/Applied",IF(K3="Yes","Dropship import error", IF(L3="Yes", "Duplicate or secondary invoice in VNet", IF(ISTEXT(E3),"Open - Dropship", IF(ISNUMBER(E3), "Open - Owned Good", "Open"))))))) Works good so far, but now I just need to figure out how to highlight and delete all the blank cells in the E column for it to work perfectly. There seems to be a lot of advice already in eliminating blank cells so that shouldn't be too much trouble. Thanks again for the help, I'm planning a crash course in VBA and I hope to come back to that function and make it work. Tom Hutchins wrote: ARGGHH! Typo time. The function call should be: =ShtComment(A3) Regarding splitting the formula across cells in 2 columns: once you are satisfied you are getting the expected results, you could hide the column with the second cell. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of IF statements exceeded...
I got it to work! I put the workbook name before the function:
=PERSONAL.XLS!ShtComment(A3) And it gave me the output anticipated. If only I would have used the Insert menu before - it's not like I was unaware of it, ugh... Thanks so much for walking me through this. Tom Hutchins wrote: When I call the function from Excel, I don't get an error. Adding data to the various columns, it returns the values we would expect. Are you sure you copied it into a VBA module (Insert Module in the Visual Basic Editor) in the same workbook? It must have the Public keyword, also. You should be able to find (and use) it by selecting Function from the Insert menu in Excel, then selecting the category User Defined. Your revised formula doesn't return "" if A3 is empty. Regards, Hutch |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count the number of caracters in a cell | New Users to Excel | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions |