Excel VBA Automatically Populating a Column

Question

question

SheliaCarr-7509 asked Feb 26, '21 Lz-3068 commented Feb 26, '21

Excel VBA Automatically Populating a Column

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]: /answers/storage/attachments/72284-capture.png

office-excel-itpro

capture.png (25.1 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-26T06:33:08.083Z

Lz-3068 answered Feb 26, '21 Lz-3068 commented Feb 26, '21

Hi @SheliaCarr-7509

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

demo.png (74.6 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SheliaCarr-7509 · Feb 26, 2021 at 08:36 AM

@Lz-3068 Thank you!! Worked perfectly!

0 ·

Lz-3068 SheliaCarr-7509 · Feb 26, 2021 at 11:18 AM

Glad I could help @SheliaCarr-7509 & Thanks for posting back

0 ·

question