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.

 

[MonicaAS]

I'm using a button with this but for some reason it isn't using the formulas. it will run with no exceptions, but nothing happens. I copied and pasted your code. any suggestions?

 

[Joe4]

Can you post your code, exactly as you have it now?
And then can you post an example of your data? You can do that using the tool mentioned here: XL2BB - Excel Range to BBCode

 

[MonicaAS]

Private Sub CommandButton1_Click()

Dim lr As Long
Dim sr As Long
Dim r As Long

Application.ScreenUpdating = False

' Find last row with data in column D
lr = Cells(Rows.Count, "D").End(xlUp).Row

' Find last populated cell in column E and start on row below that
sr = Cells(Rows.Count, "E").End(xlUp).Row + 1

' Populate columns E and F with formulas, from row 2 to last row
Range("E" & sr & ":E" & lr).FormulaR1C1 = "=IF(R[]C[-1]<>"""",R[]C[-1]+21,"""")"
Range("F" & sr & ":F" & lr).FormulaR1C1 = "=IF(R[]C[-1]<>"""",R[]C[-1]-TODAY(),"""")"
Columns("E:E").NumberFormat = "mm/dd/yy"

' Convert formulas to hard-coded values
Range("E" & sr & ":E" & lr).Value = Range("E" & sr & ":E" & lr).Value
Range("F" & sr & ":F" & lr).Value = Range("F" & sr & ":F" & lr).Value

Application.ScreenUpdating = True

' clear contents if comment is made


For r = sr To lr
If Cells(r, "G") <> "" Then Cells(r, "F").ClearContents

Next r



End Sub

 

[MonicaAS]

sorry i'm currently using my excel at work. it takes forever to get IT to approve add ins.
hope this will help

 

[Joe4]

It is because of this, I think, that we talked about in post 4 (its tough to tell because I cannot see all your data).

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.

So, the macro starts right after the last populated row in column D.
Did that condition change, regarding which rows this code should be affecting?

 

[MonicaAS]

so D is manually entered and based on the date entered in D all the calculations start there. So install date is entered the due date is the first formula where it adds 21 days. days left is based on the due date minus today.
I also checked to see if macros were disabled they look like they are good to go. if a comment is added that means the job was completed and we no longer want to see days left. so that was the last code you sent.

 

[Joe4]

Since you only sent a small excerpt your data, I cannot test it out for your situation.
If you can upload the file to a file sharing site and provided a link to it, I can download your file and test it out.
If you do that, just be sure to remove any sensitive information from the file.

 

[MonicaAS]

ok that is good. I will have to do that monday. Let me know where to send it.

 

[Joe4]

I will have to do that monday. Let me know where to send it.

Just upload it to a file sharing site like DropBox, or if you have a Google Drive account or something like that.

 

[MonicaAS]

Dropbox

www.dropbox.com