? VBA to insert formula if a column is blank or not ?
 

Hi all,

Can anybody please help.

I have the following scenario and i can't figure the vba out...!!!

I have the following columns in 'Sheet1' in cols A, B, C

COL A- Planned Date
COL B- Re-Planned Date
COL C- Actual Date

What i am trying to do is get VBA to see if there is a 'planned date'
AND a 'Replanned Date', if there is, only use the 'replanned date' and
use the following formula

i,e,,....

If The re-planned date is blank, automatically enter the following
formula into the row (Col D)

=IF($C:$C="",0,IF($A:$A=$C:$C,1,-1))

If the re-planned date is NOT blank, automatically enter the formula
into the row (Col D)

=IF($C:$C="",0,IF($B:$B=$C:$C,1,-1))

I can get the first formula to autofil, using: -

Sub FillFormula_startdate()

Range("D1").Select

ActiveCell.FormulaR1C1 = "=IF(C3=""NA"",0,IF(C1=C3,1,-1))"

Dim Rng As Range
Set Rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Rng.Offset(0, ActiveCell.Column - 1).Formula = Cells(1,
ActiveCell.Column).Formula


End Sub


Any help greatly appreciated.....




Many thanks in advance

Cheers

P :-)

? VBA to insert formula if a column is blank or not ?
 

Your formulae examples don't seem to marry with the code. Do you a test that
logically says,

IF(C=blank,0,IF(BC,1IF(AC,1,-1)))

Why do you test C1 and C3 in your code for D1, and why NA?


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Hi all,

Can anybody please help.

I have the following scenario and i can't figure the vba out...!!!

I have the following columns in 'Sheet1' in cols A, B, C

COL A- Planned Date
COL B- Re-Planned Date
COL C- Actual Date

What i am trying to do is get VBA to see if there is a 'planned date'
AND a 'Replanned Date', if there is, only use the 'replanned date' and
use the following formula

i,e,,....

If The re-planned date is blank, automatically enter the following
formula into the row (Col D)

=IF($C:$C="",0,IF($A:$A=$C:$C,1,-1))

If the re-planned date is NOT blank, automatically enter the formula
into the row (Col D)

=IF($C:$C="",0,IF($B:$B=$C:$C,1,-1))

I can get the first formula to autofil, using: -

Sub FillFormula_startdate()

Range("D1").Select

ActiveCell.FormulaR1C1 = "=IF(C3=""NA"",0,IF(C1=C3,1,-1))"

Dim Rng As Range
Set Rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Rng.Offset(0, ActiveCell.Column - 1).Formula = Cells(1,
ActiveCell.Column).Formula


End Sub


Any help greatly appreciated.....




Many thanks in advance

Cheers

P :-)