[Solved] recording macro's

    Hi I'm having a problem when I record a certain macro I've tried it several times and it wil stop and tell me I have a run time error. It always shows me that this formula is the one with the error: =IF(C21="None",0,IF(C18="HE 495",VLOOKUP(C21,Hide!R31:S32,2,FALSE),IF(OR(C18="HE 645",C18="HE 745"),VLOOKUP(C21,Hide!R29:S30,2,FALSE),IF(OR(C18="HE 986",C18="HE 1256",C18="HE 1436",C18="HE 1796",C18="HE 2066"),VLOOKUP(C21,Hide!T29:U30,2,FALSE),0)))) and when I look at it only half of it is there like it was just cut off, is it because the formula is too long? I hope this isn't too vague, here is an attachment of what I'm talking about.

    I think there is a limit of 255 characters to a cell for copying and pasting operations but normally i think you can build very long formulae. But maybe the macro recorder has a similar limit to the copy/paste operation.


    I must admit the formula is quite long and complex. Have you thought of a VLOOKUP of your options instead of using all those IF statements?


    Could you arrange your data different to make it easy to figure out? Maybe build some of the IF logic into a table and look up the table after the hard work has been done in the table itself.


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

    Hi Brian,
    (I remember that file from before.) Could you explain what you are trying to accomplish this time? What cells on what sheet needs filling in with what information? Perhaps we can help you find a better way.

    Well right now I have it set up on sheet 1 so when you push one of the buttons on the custom toolbar it will run the macro for that model but when I recorded the one for the multi tube it stopped at that long formula. So essentaily what I'm doing is copying everything from the other 3 sheets that where my out put pages and making it into one simple sheet. Iwill also have the same problem when I do the V Tube because in the same spot on that one there is an even longer formula(which you can see on the Mini, Multi,V Tube sheet). Right now I'm working on making them shorter hoping that will work.

    See if what I did with V-Tube is acceptable. I first went in and changed all of the cell references on the V-Tube table that referred to other sheets to absolute references (you might consider named ranges, to make debugging easier). Then I wrote a macro that just copied that table to wherever the activecell was on sheet 1 (see the macro VTube). If this does what you want, then try it for the other models, but remember to make the references to other sheets absolute so the correct values are returned.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login