Write Formula to Cell with "If True" Blank cell option

    I have a formula I am trying to write to a cell, via code.. however, it keeps erroring at the "If solves True" then "" I have tried """" and ("""") but nothing works. If I end the formula before that part, it will write it.. any ideas?

    Code
    'Selects the row you added
Range("A" & (Target.Row)).Select
 
'Next Row down and read in Row Number
RowNum = Target.Row + 1
 
 
Range("A" & RowNum & "").Offset(0, 4).Formula = "=IF(ISBLANK(D" & RowNum & "),(""""),ROUND(ROUND((V" & RowNum & "-T" & RowNum & "+(V" & RowNum & "<T" & RowNum & "))*1440,0)/60" '= "=sum(A" & RowNum & ":B" & RowNum & ")"



    I am baffled... Thanks in advance.....

    Re: Write Formula to Cell with &quot;If True&quot; Blank cell option


    Try doing it this way..(ie joining the end parentheses of the ISBLANK function with the ISLBLANK argument, rather than joining it with the rest of the expression)


    Code
    "=IF(ISBLANK(D" & RowNum & ")" & ",(""""),R...


    To re-iterate, just as an example, the following formula works, so probably just
    need to keep the end bracket in with the ISBLANK argument


    Code
    Range("A" & 5).Formula = "=IF(ISBLANK(D" & RowNum & ")" & ","""",90)"

    Re: Write Formula to Cell with &quot;If True&quot; Blank cell option


    Quote

    so probably just need to keep the end bracket in with the ISBLANK argument



    I would say so as when you're trying to write formulas in VBA, text (including null) needs to to be put within double quotation marks, not brackets.

    Re: Write Formula to Cell with &quot;If True&quot; Blank cell option


    You have two ROUND functions, but not enough arguments for both, and you have 7 opening parentheses but only 5 closing ones as far as I can see. I suggest you add the formula you want manually to a cell, then check its Formula property.
    Your formula would also, IMO, be a lot simpler to write in R1C1 syntax - at a guess:

    Code
    Range("A" & RowNum & "").Offset(0, 4).FormulaR1C1 = "=IF(ISBLANK(RC4),"""",ROUND((RC22-RC20+(RC22<RC20))*1440,0)/60)"

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Write Formula to Cell with &quot;If True&quot; Blank cell option


    Rory and all,

    I am getting a test workbook put together.. This post here is when I was getting the formula built last July. I thought there was more to the post because I remember a part where after it worked (more or less) the times were still off. It's calculating flying times, based on hours and tenths. The end of the post isn't there.. but it continued leaving me with the current (and working) sheet based formula I posted. If you goto the other thread to see how the formula was built (mostly - sans the ending part) it might help fix it so I can write it using VBA.

    The reason am moving it to being written via VBA, is I have code that when you double click in Column A it will ask you, do you want to insert a new line.. (sometimes data has to be inserted after everything is filled out, thus needing to insert a line. When the line is inserted, it inserts a blank line. The requirement of this post is how to now write the formula to the Cell via VBA after the new line was inserted.

    I can get simple formulas to write to the cell, but as soon as it gets near the null or the blanking arugment, it errored. I tried your suggestion Rory, and it does write it to the sheet, however when it's used to calculate the time, it just displays 0.0 ... read the development thread of the formula to under stand the formula.. and like I said, there was another page, because it ends in showing a round down and doesn't show us working out the tenths error after the 30 min mark where he has to take a tenth of a second off the time for it to calculate right.. that might be where the double rounds comes from.

    I will see if I can find in my e-mail tomorrow the e-mail from OzGrid telling me about the post last July and see if its still there.

    Here is the Development of this particular formula for sheetbased usage:
    http://www.ozgrid.com/forum/showthread.php?t=144128

    I will finish the test workbook so you can see it working tomorrow, thanks for your help everyone!!!

    Joe

Participate now!

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

Register Yourself Login