VBA Macro to tell a cell to remain blank

**jonvanwyk** · 12-12-2010, 08:15 PM

I cannot use a regular formula because the cell already contains a complicated formula that is doing something else. Can I write some VBA for cell T36 that says something to the affect:

IF T35 IS BLANK, THEN T36 SHOULD ALSO BE BLANK. Ideally, this macro would override any other instructions T36 has.

**teylyn** · 12-12-2010, 08:23 PM

Why can't you do this with the formula you already have in place?

T36 =if(Isblank(T35),"",<your current formula>)

**jonvanwyk** · 12-12-2010, 08:26 PM

Originally Posted by teylyn

Why can't you do this with the formula you already have in place?

T36 =if(Isblank(T35),"",<your current formula>)

Maybe I can....I was not aware of that function. Can you help me insert it? My formula is rediculously long.

=IF(OR(C5="V1",C5="V2",C5="V3",C5="P1",C5="P2",C5="P3",C5="CT1",C5="CT2",C5="CT3",C5="S1",C5="S2",C5="S3",C5="FS1",C5="FS2",C5="FS3",C5="W1",C5="W2",C5="W3",C5="M1",C5="M2",C5="M3",C5="CW1",C5="CW2",C5="CW3",C5="F1",C5="F2",C5="F3",C5="PAL1",C5="PAL2",C5="PAL3",C5="FMLA S1",C5="FMLA S2",C5="FMLA S3",C5="FMLA FS1",C5="FMLA FS2",C5="FMLA FS3",C5="FMLA V1",C5="FMLA V2",C5="FMLA V3",C5="FMLA P1",C5="FMLA P2",C5="FMLA P3",C5="FMLA CT1",C5="FMLA CT2",C5="FMLA CT3",C5="FMLA W1",C5="FMLA W2",C5="FMLA W3",C5="FMLA",C5="UAL"),(B6-B5+(B6<B5))*24,TEXT(B6,"hh:mm"))

Thank you in advance if you are able to further assist me. I truly cannot believe how helpful this forum is. If all other Excel resources were to fall off the face of the internet, I think the novice would not be worse off due to excelforum.com!!!

**jonvanwyk** · 12-12-2010, 08:35 PM

Originally Posted by teylyn

Why can't you do this with the formula you already have in place?

T36 =if(Isblank(T35),"",<your current formula>)


=IF(ISBLANK(T35),"",IF(OR(C5="V1",C5="V2",C5="V3",C5="P1",C5="P2",C5="P3",C5="CT1",C5="CT2",C5="CT3",C5="S1",C5="S2",C5="S3",C5="FS1",C5="FS2",C5="FS3",C5="W1",C5="W2",C5="W3",C5="M1",C5="M2",C5="M3",C5="CW1",C5="CW2",C5="CW3",C5="F1",C5="F2",C5="F3",C5="PAL1",C5="PAL2",C5="PAL3",C5="FMLA S1",C5="FMLA S2",C5="FMLA S3",C5="FMLA FS1",C5="FMLA FS2",C5="FMLA FS3",C5="FMLA V1",C5="FMLA V2",C5="FMLA V3",C5="FMLA P1",C5="FMLA P2",C5="FMLA P3",C5="FMLA CT1",C5="FMLA CT2",C5="FMLA CT3",C5="FMLA W1",C5="FMLA W2",C5="FMLA W3",C5="FMLA",C5="UAL"),(B6-B5+(B6<B5))*24,TEXT(B6,"hh:mm")))

I believe I correctly inserted your solution, but it did not fix the problem. In this particular instance, if no label is applied to C5, then no math needs to be done, and B6 is referenced. However, when C5 is blank, and B6 is also blank, then T36 is showing 00:00. Please note that the advanced settings already have the box deselected that adds a 0 for blank cells.

Using the above referenced formula, everything is calculated correctly. The problem is when the form is blank and nothing has yet been selected or entered, I have 00:00 all over the place. :-(