Excel VBA Automatically Populating a Column

Shelia Carr 136 Reputation points
2021-02-26T04:02:55.787+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,671 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2021-02-26T06:33:08.083+00:00

    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  
    

    72278-demo.png


0 additional answers

Sort by: Most helpful