Only calculate formula if cell is not blank

[MonicaAS]

In one column I have a date field. If there is no date I don't want the other columns to calculate the formula, using VBA.
Column c: 1/20/20 Column D: "formula is column c +21 days column E: "column d minus today.
its all based on the date field in column C.

 

[Joe4]

Welcome to the Board!

Assuming the first row with data in it is row 2, try this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long

    Application.ScreenUpdating = False
   
'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row
   
'   Populate columns D and E with formulas, from row 2 to last row
    Range("D2:D" & lr).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]+21,"""")"
    Range("E2:E" & lr).FormulaR1C1 = "=IF(RC[-2]<>"""",RC[-1]-TODAY(),"""")"
    Columns("E:E").NumberFormat = "0"
   
'   Convert formulas to hard-coded values
    Range("D2:D" & lr).Value = Range("D2:D" & lr).Value
    Range("E2:E" & lr).Value = Range("E2:E" & lr).Value
   
    Application.ScreenUpdating = True
   
End Sub

 

[MonicaAS]

Thanks so much! one other question. this is potentially a large database is there a way for it to move down a line when it is looking for the formulas? so it doesn't recheck line 2-10 etc.

 

[Joe4]

Thanks so much! one other question. this is potentially a large database is there a way for it to move down a line when it is looking for the formulas? so it doesn't recheck line 2-10 etc.

What do you mean?
Are you saying that you will continuously be adding new data to your sheet, and running this macro multiple times?

If so, you can look at column D for the last populated row, and then start in the next row.
Something like this:

Sub MyMacro()

    Dim lr As Long
    Dim sr As Long

    Application.ScreenUpdating = False
    
'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Find last populated cell in column D and start on row below that
    sr = Cells(Rows.Count, "D").End(xlUp).Row + 1
    
'   Populate columns D and E with formulas, from row 2 to last row
    Range("D" & sr & ":D" & lr).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]+21,"""")"
    Range("E" & sr & ":E" & lr).FormulaR1C1 = "=IF(RC[-2]<>"""",RC[-1]-TODAY(),"""")"
    Columns("E:E").NumberFormat = "0"
    
'   Convert formulas to hard-coded values
    Range("D" & sr & ":D" & lr).Value = Range("D" & sr & ":D" & lr).Value
    Range("E" & sr & ":E" & lr).Value = Range("E" & sr & ":E" & lr).Value
    
    Application.ScreenUpdating = True
    
End Sub

 

[MonicaAS]

One last question, if there are comments in field G I want to clear contents in column F this is what I tried
If Range("G" & sr & ":G" & lr) <> "" Then
Range("F" & sr & ":F" & lr).ClearContents

End If

 

[Joe4]

One last question, if there are comments in field G I want to clear contents in column F this is what I tried
If Range("G" & sr & ":G" & lr) <> "" Then
Range("F" & sr & ":F" & lr).ClearContents

End If

You cannot do an IF statement on a whole range of values at once like that.

When you say there are "Comments" in column G, do you mean just something typed in the cell, or a "Cell Comment" (where you have the little colored flag in the upper right corner of the cell, and if you select the cell, it will show you what the comment says, and if you move off of it, it will be hidden and all you see is the colored triangle)?
Here is a deeper explanation of Excel Comments: Insert comments and notes in Excel

 

[MonicaAS]

Just text. Not the pop up kind

 

[Joe4]

One way is to use a loop, i.e.

Dim r as Long
For r = sr to lr
    If Cells(r,"G")<>"" Then Cells(r,"F").ClearContents
Next r

 

[MonicaAS]

thank you!

 

[Joe4]

You are welcome.