VBA code to update today's date based on specific conditions.

[amaresh achar]

Hi,

Can anyone please help me with VBA code to perform below task:

1. Check the existing value of cell R5.
2. If R5 is empty, then update today's date in dd-mmm-yyyy format.
3. If R5 has a valid date in dd-mmm-yy or dd-mmm-yyyy format, then DO NOT update today's date.
4. If R5 has anything except a valid date, then update today's date in dd-mmm-yyyy format.

Thanks in Advance.

 

[kevin9999]

Try the following:

Option Explicit
Sub GetDateIf()
    Dim ws As Worksheet, r As Range
    Set ws = Worksheets("Sheet1")       '<~~ *** Change to actual sheet name ***
    Set r = ws.Range("R5")
    
    If Not IsDate(r) = True And (r.NumberFormat = "dd-mmm-yy" Or r.NumberFormat = "dd-mmm-yyyy") Then
        r = Date
        r.NumberFormat = "dd-mmm-yyyy"
    End If
End Sub

 

[amaresh achar]

Try the following:

Option Explicit
Sub GetDateIf()
    Dim ws As Worksheet, r As Range
    Set ws = Worksheets("Sheet1")       '<~~ *** Change to actual sheet name ***
    Set r = ws.Range("R5")
   
    If Not IsDate(r) = True And (r.NumberFormat = "dd-mmm-yy" Or r.NumberFormat = "dd-mmm-yyyy") Then
        r = Date
        r.NumberFormat = "dd-mmm-yyyy"
    End If
End Sub

Sorry Sir, I tried, but didn't work.

 

[kevin9999]

Sorry Sir, I tried, but didn't work.

I'm afraid "didn't work" tells me very little. Did it not change the value in R5? Did it give an incorrect result? Did Excel freeze/crash? Perhaps you could share your workbook via Dropbox, Google Drive or similar file sharing platform? Or at the very least provide a copy of your worksheet using the XL2BB add in?