Entering Today's Date based on values in two different cells (VBA)

[JayB0730]

Hello,

I want to create code where IF cells in Column A are populated & Column I are not populated insert today's date into Column I. I do not want a set range (i.e. A1:A100) as my spreadsheet will continue to increase over time.

Thank you in advance!
Jay

 

[Joe4]

Are there any gaps in the middle of column A?
For example, could you have A1 populated, A2 not populated, A3 populated?
Or will all the populated cells in column A be together with no gaps in between?

 

[Joe4]

If there are no gaps in your column A data, try this:

Sub MyDateInsert()

    Dim lRow As Long
    Dim rng As Range
    
'   Find last row with data in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set column I range
    Set rng = Range("I1:I" & lRow)
    
'   Find blank cells in column I
    rng.SpecialCells(xlCellTypeBlanks).Value = Date

End Sub

 

[JayB0730]

Hi Joe,

This is great, thank you! After I run the Macro & attempt a second time it brings up an error message...is there a way to make it so that a message pops up to alert the user that there are no dates that need to be entered (or whatever message I want to appear)?

Thanks,
Jay

 

[Joe4]

Sure, like this:

Sub MyDateInsert()

    Dim lRow As Long
    Dim rng As Range
    
'   Find last row with data in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set column I range
    Set rng = Range("I1:I" & lRow)
    
'   Find blank cells in column I
    On Error GoTo err_chk
    rng.SpecialCells(xlCellTypeBlanks).Value = Date
    On Error GoTo 0
    
    Exit Sub
    

err_chk:
    If Err.Number = 1004 Then
        MsgBox "No dates need to be entered!", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[JayB0730]

Joe,

Worked like a charm. Thank you so much! As I am a n00b to VBA & trying to learn as I go, the comments that you included really helped. Thanks again.

- Jay

 

[Joe4]

You are welcome!
Glad I was able to help.