Issue with setting a cell to a formula

[Lsxtrkiller]

Okay,

So i have a very log IF formula

Please Login or Register  to view this content.

The formula works as intended.

However when i try to work it into my vba script it give me an error "expected end to IF statement" I did not have this issue before I modified the formula to include if E2=4 statements.

I have tried this as a R1C1 formula and as just formula and i get the same error either way. Did i botch something in the formula or is there an issue with the VBA. Its probably something very simple ive just been looking at this and similar code for too long right and i feel like i am just overlooking it. A second set of eyes might help.

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

By the way, I think you might want to consider creating some named ranges because there's a lot of iteration in that code.

For example if you created a named ranges called LOG_W, LOG_Z and LOG_AK for 'WIP LOG'!$W:$W, 'WIP LOG'!$Z:$Z and 'WIP LOG'!$AK:$AK respectively the code/formula would look something like this.

Please Login or Register  to view this content.

[Lsxtrkiller]

That instantly sets my cells in H2 to false and doesn't set them to the formula.

[JBeaucaire]

It's too long, VBA has a limit on the number of characters it can enter into a cell.

Can you turn that formula into a NAMED FORMULA instead?

1) Click on the cell that exact dynamic formula would go into
2) CTRL+F3 to open the name wizard
3) Create a new name called MyFormula and put that formula in the RefersTo: field

4) Now, back in the original cell you can call this lengthy formula with a simple =MyFormula

Obviously that very short formula would work fine being entered by VBA.

[Norie]

The code/formula works fine for me.

[Lsxtrkiller]

@JBeaucaire That worked perfectly

I have never used the name wizard like that, its nifty.

Thank you!

@Norie thats odd i am not sure why mine didnt, i copied it directly in.

[Lsxtrkiller]

I just noticed an issue @JBeaucaire when I do that the formula becomes sheet specific. if i look at the formula it becomes

Please Login or Register  to view this content.

which for one didnt keep the whole formula and two if i try to use it on another sheet it still refers back to this sheet M27532-1.

[Lsxtrkiller]

@Norie you code worked i am not sure what i did the first time i tried but I added one line to it and its not working again take a look below. (application-defined or object-defined error)

I will take your suggestion and name the ranges and change all of my code tomorrow as it does look a lot cleaner.

Please Login or Register  to view this content.

[Norie]

Can't immediately see what you changed/added but I'll maybe have a closer look later.

By the way, I think you might be able to simplify your formula some.

For example, as far as I can see anyway, something like =IF($E2=0,IF($D2=0, ... is basically the same as IF (AND($E2=0, $D2=0), ...

You have a lot of these 'double IFs' in the formula and I think if you were able to replace them with ANDs it might tidy the code up a little further, and make it more readable.