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

 

[Rick Rothstein]

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

I am still not sure about the 8 digits part, but if you are correct in the assumptions embodied in your code, then the result can be achieved without using a loop. This code will do the same as your code does...

Private Sub Workbook_Open()
  With Range("I2", Cells(Rows.Count, "I").End(xlUp))
    .Cells = Evaluate("IF(" & .Address & "=""""," & .Offset(, 4).Address & "&TEXT(TODAY(),"" mm/dd/yyyy"")," & .Address & ")")
  End With
End Sub

 

[MARK858]

then the result can be achieved without using a loop. This code will do the same as your code does...

I thought there would be a way to do it with Evaluate but I haven't got used to the syntax yet (and lack of time to practice these days) so I am pleased you posted the reply so I have something else to look back at when I get the chance.

 

[jevi]

Hi Pluff, Thank you for your vba code. It did what I need even though I had to put ( and not (,) as here we use ;. But then it showed the formula in the cell as:
=RIGHT(B2; 8)

So I had to clear formats; put in F2 and then press enter. Still Thank you so much .

 

[jevi]

Thank you Mark, sorry if my eplanation was not good.

Still your second code worked as a treat for the date.

But I wanted for the same sheet1 which in column B2:B200, I have text with 15 digits, I needed to put in column M2:M200 with 8 digits. So I thought it should be a way to combine both the VBA code. Thank you.

 

[jevi]

Hi Rick, sorry if my explanation was not good.

I wanted to combine the VBA of Mark (the second one at the begining for the date) with another work I have to do for the same sheet.

So I have in columns B2:B200, text with 15 digits, I needed to put them in column M2:M200 (or even at the same column B2B:B200) with 8 digits.

Please, let me know if I am not clear. Thank you so much for your kind help to all.