Excel VBA Automatically Populating a Column

Question

I'm trying to populate column R with the current date using the =TODAY() function but each row should only populate the date if there's data existing on the Row based in column B. So if there's an Invoice number on the row in column B, then column R will be populated with the current date. The data in column B would already be existing prior so it's not the code when there's data entered in column B, then column R would populate the date. Maybe row count on column B and then have the returned number of rows populated in Column R with the current date. I never want column R to populate a date on more or less rows than what was sent to me in Column B. ![72284-capture.png][1] [1]: /api/attachments/72284-capture.png?platform=QnA

Answer 1

Hi @Shelia Carr

If it was for me I would format my dataset as a Table and work with a ListObject to limit the number of rows to "scan" + wherever the dataset sits in the worksheet the code would still work. With your current setup if you add one of more rows before your current row 1 (header row) you'll have to revise the below code:

Sub foo()  
    Dim r       As Long  
      
    ' Row    #1     : Header  
    ' Column #2 (B) : Invoice number  
    ' Column #18 (R): Invoice received date  
      
    With ActiveSheet.UsedRange  
        For r = 2 To .Rows.Count  
            If Len(Cells(r, 2)) > 0 Then Cells(r, 18) = Date  
        Next r  
    End With  
End Sub