VBA Code for Entering Today Function

[jevi]

Hello, I am trying this VBA as I need in sheet1 at column starting from i2, if i2 is empty to put the today date, if cell i2 has a date it should leave the old date. I did a formula =IF(I2="";TODAY();I2),but I was still thinking why the code is not working.
Maybe I should put .cell (i2) and not range.

Private Sub Workbook_Open()With Sheets("sheet1").Range("I2:I200")If .Value = "" Then .Value = DateEnd WithEnd Sub

thank you

 

[Norie]

Are you trying to put today's date in the next empty row in column I?

If so try this.

Private Sub Workbook_Open()

    Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Offset(1).Value = Date()

End Sub

 

[MARK858]

If on the other hand you are trying to fill each blank cell in the range with today's date then try...

Private Sub Workbook_Open()
    Dim myCell As Range
    Application.ScreenUpdating = False
    For Each myCell In Sheets("sheet1").Range("I2:I200")
        With myCell
            If .Value = "" Then .Value = Date
        End With
    Next
    Application.ScreenUpdating = True
End Sub

or (will only work if the cells are truly blank).

Private Sub Workbook_Open()
Sheets("sheet1").Range("I2:I200").SpecialCells(4).Value = Date
End Sub

 

[jevi]

Thank you Norie, yes today'date when it find a cell empty. Your VBA it works only for the latest rows I see and not in between, i might happen that some rows have date and some not. Let's say I5 has a date, than I8 doesn't have a date and here I need today's date.

 

[jevi]

Mark, you rock... tried only the second one as some of my cells are blank and it worked super that I needed . Perfect thank you so much. The first one worked as you said so if all cells are empty and I need to put today's date.

Thank you once again for your help, specially on weekend day .

 

[Rick Rothstein]

Thank you Norie, yes today'date when it find a cell empty. Your VBA it works only for the latest rows I see and not in between, i might happen that some rows have date and some not. Let's say I5 has a date, than I8 doesn't have a date and here I need today's date.

Is there a defined range to check? Otherwise, what is considered at the last row beyond which a date will not be placed? Or do you want all blanks to the very last row filled?

Also, what format do you want your dates displayed as?

 

[jevi]

Rick thank you, no it is fine with second VBA of Mark, it did what I requested. The last row in my case was when there was no more data in it.
But maybe kindly you can help combine both VBA as I also need to have a text in 8 digits and I am not working it out.
Maybe I need to put also the sheet name? to combine the date and the digits in one macro...I don't mind using even separate though. Thank you for your help.

Private Sub Workbook_Open()Range("B2:B200").Value = Right(Range("M2:M200").Value, 8)
End Sub

 

[Fluff]

How about

Private Sub Workbook_Open()

    Dim UsdRws As Long
    
    With Sheets("Sheets1")
        UsdRws = .cells.Find("*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error Resume Next
        .Range("I2:I" & UsdRws).SpecialCells(4).Value = Date
        On Error GoTo 0
        .Range("B2:B" & UsdRws).Formula = "=Right(M2, 8)"
        .Range("B2:B" & UsdRws).Value = .Range("B2:B" & UsdRws).Value
    End With
    
End Sub

 

[Rick Rothstein]

But maybe kindly you can help combine both VBA as I also need to have a text in 8 digits

Can you explain exactly what you mean by the above statement taken from Message #7 ?

 

[MARK858]

Not sure I am understanding your question exactly but try the code below on a copy of your data and change the date format to suit.

Private Sub Workbook_Open()
    Dim myCell As Range
    Application.ScreenUpdating = False
    For Each myCell In Sheets("sheet1").Range("I2:I" & Sheets("sheet1").Range("I" & Rows.Count).End(xlUp).Row)
        With myCell
            If .Value = "" Then .Value = Date
            .Value = Right(.Offset(, 4).Value, 8) & " " & Format(.Value, "mm/dd/yyyy")
        End With

    Next
    Application.ScreenUpdating = True
End Sub