Number of IF statements exceeded...

[gardenhead]

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

[Tom Hutchins]

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

[gardenhead]

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

[Tom Hutchins]

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

[Tom Hutchins]

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

[gardenhead]

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

[Tom Hutchins]

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

[Tom Hutchins]

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

[gardenhead]

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