Vba to subtract current date from date in column G and put static diff in column H

I have searched through many threads and many websites and can't find the pieces to accomplish this task.

I have a date in column G of my worksheet and I want to subtract the current date from it and put the difference (either positive or negative) in column H.

I want this to be done when the workbook is opened but once the calculation has populated column H it should never update it. In other words, if I open the workbook and column H is populated and I subsequently save and close the workbook, the next time I open it the calculation will leave the already existing values in column H alone.

This is a document metric workbook stored in a restricted location so no one else can save it back to that location. It is dynamically updated with current dates that documents are, or will be, due for review. Column H will be negative if the doc is already overdue, or positive if it is not yet due. Since we grab a "current" update once a week, we want to be able to store it in another location and not have the metric in column H change when we open the workbook at a later date.

You can modify the ThisWorkbook_Open event in VBA to add some code to check whether the cells are populated and if not perform the calculation.

Thanks
Duncan

Thanks, that helps with one of my issues. The one I've spent all day on is the math to get the number for column H.

Try:

Sheets([Sheet name]).Range([insert cell here]).Value = Sheets([Sheet name]).Range([Column G]).Value - Date

I'm guessing you meant "Worksheets" rather than "Sheets". Regardless, I get a "Type mismatch error" with this...

Worksheets(Sheet1).Range("S:S").Value = Worksheets(Sheet1).Range("G:G").Value - Date

You can use Sheets("Sheet1").Range too

Try this:

Dim c As Range

For Each c In Sheets("Sheet1").Range("H:H")
    If c.Value = Empty Then
    c.Formula = "=" & Cells(c.Row, c.Column - 1).Address(0, 0) & "-TODAY()"
    End If
Next c

This will test to see if the cell is empty first, then apply the formula in the cell.