Need help to unravel nested if statement in formula. (fomula to VBA code)

[Richard U]

I have this formula

=IF(OR((IF(‘Sheet 1’!AJ6=0;""; ‘Sheet 1'!AJ6))="";(IF(‘Sheet 1'!AJ6=0;""; ‘Sheet 1'!AJ6))=" ");"~";IF(LEFT(‘Sheet 1'!AM6;3)="940";"RSW INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="401";"REU INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="200";"ANZ INPUT";IF(RIGHT($M6;2)="MX";"#N/A";IF(‘Sheet 1'!AN6="COMPANY NAME";"RNA INPUT";VLOOKUP(E6&" "&(IF‘Sheet 1'!AJ6=0;"";'Portal Data'!AJ6));Sarep!$B$4:$C$291;2;"FALSE")))))))


Which I need to translate into some maintainable VBA code, but I can't quite figure out what this is doing. I'm a good coder, but the nested excel formulas confuse me.


Could someone please explain what this formula is doing? I can write the code myself once I understand the logic, but I can't seem to unravel this myself

 

[jimrward]

Try changing your delimiter in windows regional option from comma to semi colon and you can all have semi colons in a formula so possibly NOT a syntax error

 

[thisoldman]

Cerfani,
Are you sure about that substitute conditional test?

Here's a little data table:

		OR()	=0
space		TRUE	FALSE
blank		TRUE	TRUE
empty string		TRUE	FALSE
zero	0	TRUE	TRUE

<tbody>
</tbody>

B2 contains a space, " ".
B3 is blank.
B4 contains an empty string, =ISBLANK(B4) returns false.
B5 contains a zero, 0.

In cell C3 and dragged down:
=OR(B2=0, B2="", B2=" ")

In cell D3 and dragged down:
=B2=0

 

[cerfani]

yeah im sure... he is just checking ‘Sheet 1’!AJ6=0 and if true he replaces with "" then he checks if the value returned was "" ... so he can just check for the original boolean