question

coachz-9206 avatar image
0 Votes"
coachz-9206 asked MotoX80 commented

Excel UDF Function if Statement giving me error

Hello , The UDF function works only for one of the conditions and errors if there is more than one, using else if did not want to work, What am I doing wrong? Thank you


Function Calc(rowaddress)
If (cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="P"
And cells(rowaddress.row,16).value=0

And cells(rowaddress.row,10).value<24)
then Calc=24-cells(rowaddress.row,10).value
Else If (cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="O"

And cells(rowaddress.row,16).value<>0
And cells(rowaddress.row,10).value>24)

then Calc=24+cells(rowaddress.row,10).value
else calc=0

I inherited a long formula that a function will do wonders with it, it has 9 different conditions, any help will be greatly appreciated.

Thank you

windows-10-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What error do you get?

Just looking at the code, I'd suggest putting parens around the "or" tests.

 Function Calc(rowaddress)
 If ((cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="P")
 And cells(rowaddress.row,16).value=0
 And cells(rowaddress.row,10).value<24)
 then Calc=24-cells(rowaddress.row,10).value
 Else If ((cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="O")
 And cells(rowaddress.row,16).value<>0
 And cells(rowaddress.row,10).value>24)
 then Calc=24+cells(rowaddress.row,10).value
 else calc=0


0 Votes 0 ·
coachz-9206 avatar image
0 Votes"
coachz-9206 answered MotoX80 commented

It is amazing how “else” in separate line makes the difference, your help was really appreciated and I would love to buy you lunch if I can, you made my day.
Thank you
Coach z

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If the code I posted solved the problem, you could mark that as the answer. Thanks.

0 Votes 0 ·
coachz-9206 avatar image
0 Votes"
coachz-9206 answered

Thank you.
Error says end without if block, when I look at the code the system change my Else if to Else: If.
The formula works only when I leave only the first condition and remove the others, others comes with #value! In the cell result.
Appreciate your help.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered

VBA is ElseIf.


https://www.educba.com/vba-else-if/



 Function Calc(rowaddress)
     If ((cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="P")
             And cells(rowaddress.row,16).value=0
             And cells(rowaddress.row,10).value<24)
     then 
         Calc=24-cells(rowaddress.row,10).value
     ElseIf ((cells(rowaddress.row,6).value="F" or cells(rowaddress.row,6).value="O")
             And cells(rowaddress.row,16).value<>0
             And cells(rowaddress.row,10).value>24)
     then 
         Calc=24+cells(rowaddress.row,10).value
     else 
         calc=0
     End if
 End Function  

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.