CODEWITHSUNDEEP
vbaexceldateautofill
psmith
psmith

Reputation: 11

xlfillseries is not working in userform for vba?

I am trying to autopopulate consecutive dates based off of a value in a text box on a userform. When the code is run however, the value is simply copied in adjacent textboxes. (Ie C2 has a value of 2-May, C3 has a value of 2-May etc.)

Why is this not working?

datetxtbx.Value = Format(Now, "d-mmm")
Range("C2").Value = datetxtbx
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:I2"), Type:=xlFillSeries

Upvotes: 1

Views: 166

Answers (1)

user4039065
user4039065

Reputation:

You are specifically putting text-that-looks-like-a-date into a cell and expecting it to act like a true numerical date. You might as well put abc into a cell and try to series fill it into adjacent cells. You will just end up with a bunch of abc's.

The following takes the current date as a string into the textbox's value. It converts the text-that-looks-like-a-date into a true date before putting it into C2. With a true date in C2 you need to set the cell's number format if you want to display 2-Jun. After this the .AutoFill will sequence the date in C2 into the prescribed target cells and bring the cell formatting along for the ride.

datetxtbx.Value = Format(Date, "d-mmm")
Range("C2").Value = CDate(datetxtbx)
Range("C2").NumberFormat = "d-mmm"
Range("C2").AutoFill Destination:=Range("C2:I2"), Type:=xlFillSeries

Note that I've changed your Now to Date. Date is the current date; Now is the current date and time. It seems you really only want the current date.

Upvotes: 2

Related Questions