How to insert and drag the formula till the last row in Excel VBA

Question

I would like to insert the formula which calculate only 1st day of everymonth (1.Nov.2017, 1.Dec.2017 etc.) at the end of E row and drag it till the end which equal to values of D row. I used the code below but not working.

I need Value in "E12 : E21 " as 01.Nov.2017 only if A:D have data. But A:D will be automatically calculated. For next month A22 :D24 will contain data. So i need values in "E22: E24 " as 01. Dec.2017. Help me

Private Sub CommandButton1_Click()

Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Select
Run FirstDayInMonth()
Selection.AutoFill Destination:=Range("D" & Column.count).End(xlUp).Offset(0, 1), Type:=xlFillCopy

End Sub

Function FirstDayInMonth(Optional dtmDate As Date = 0) As Date
Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Select
    If dtmDate = 0 Then
       dtmDate = Date
    End If
FirstDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate), 1)
End Function

`I used the code below but not working` ... tell me, which part of this sentence gives really good information about the problem that you are having? — jsotola, Nov 24 '17 at 08:21
I'm not clear on what you need either. You want a calculation in column D where there's data on the same row in column C? Is there a reason this has to be done using VBA? — ashleedawg, Nov 24 '17 at 08:55
@jsotola For every month Column A to D will be updated automatically, but not E Column. I need to add 01.nov.2017 for and for next month 01.dec.2017 and so on. It must be on the E rows. i,e "E12" till the end. for next month "E22" and till the end. — Deepak, Nov 24 '17 at 09:28

MarcinSzaleniec · Accepted Answer · 2017-11-24T12:48:05.453

1

At first, you overuse Select. It should be use in the code in one case only - if you want macro to point certain cell at the end. See this article, for example.
Secondly, avoid Smart UI antipattern. What is Smart UI, you can read here:

Third, I think you should use sub, not function here.

Sub FillFirstDay(Optional dtmDate As Double = 1)

Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long, firstRow As Long

Set ws = ActiveSheet 'You should assign your sheet here, for example by name

'Then we find our range in E column
With ws
    lastRow = .Range("D" & .Rows.Count).End(xlUp).Row
    firstRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1
    Set rng = Range(.Range("E" & firstRow), .Range("E" & lastRow))
End With

If firstRow >= lastRow Then Exit Sub

'And finally add first date of month
With rng
    .Value = DateSerial(Year(dtmDate), Month(dtmDate), 1)
    .NumberFormat = "yyyy-mm-dd" 'or whatever date format do you like
End With

End Sub

The line If firstRow >= lastRow Then Exit Sub terminates the procedure when dates in column E are already filled.

edited Nov 24 '17 at 12:48

answered Nov 24 '17 at 08:57

MarcinSzaleniec

2,246
1
7
22

I get an **Application Defined Error** with the default value with you code when it's parsing as `range("$E$20").Value = DateSerial(1899,12,1)` – ashleedawg Nov 24 '17 at 09:12
well, this is interesting issue, worth of another question. Anyway, if optional parameter is 2, everything should be OK. – MarcinSzaleniec Nov 24 '17 at 09:58
@MarcinSzaleniec - that's what you said about 1... Two could be _twice_ as bad. :-) – ashleedawg Nov 24 '17 at 10:12
Now i uploaded my new image. Check the output, plz. – Deepak Nov 24 '17 at 10:24
well, for me optional 1 doesn't work, but option 2 is OK. Anyway, you would not propably need dates from the beginning of the previous century, so you can drop Optional clause at all. – MarcinSzaleniec Nov 24 '17 at 10:46
@MarcinSzaleniec Hey man your code is working almost. but one small problem. The date is replacing the last value in E column. i,e E11 changes to 01.Nov.2017. (I deleted dtmDate and mentioned as Date) – Deepak Nov 24 '17 at 10:52
1

just add offset when setting rng: Set rng = Range(.Range("E" & .Rows.Count).End(xlUp).Offset(1), .Range("E" & lastRow)) – MarcinSzaleniec Nov 24 '17 at 11:30
I added offset but now another problem. If i am clicking the program 2nd time , the date value is added on cell E22 and its going on down. I need to avoid that because there is no value on A22.D22 – Deepak Nov 24 '17 at 12:30
Just edited my answer. There is plenty of way you can define range in E column for fill. – MarcinSzaleniec Nov 24 '17 at 12:50
Thank you. works Perfectly. But delete `Optional dtmDate As Double = 1` and replace `dtmDate` to `Date`. It will work perfectly. – Deepak Nov 24 '17 at 13:05

ashleedawg · Answer 2 · 2017-11-24T09:58:59.330

0

Perhaps I'm missing a reason that this has to be done in a complicated fashion, but can't you just use a worksheet function?

You want Column E to show a date (the 1st of the month) in rows where there's data?

Put this into cell E2 (or all of column E if you want), either directly or programmatically (with WorksheetFunction):

=IF(D2="","",DATE(YEAR(NOW()),MONTH(NOW()),1))

Modified formula:

edited Nov 24 '17 at 09:58

answered Nov 24 '17 at 09:22

ashleedawg

20,365
9
72
105

For every month Column A to D will be updated automatically, but not E Column. I need to add 01.nov.2017 for and for next month 01.dec.2017 and so on. It must be on the E rows. i,e "E12" till the end. for next month "E22" and till the end. – Deepak Nov 24 '17 at 09:27
okay. Would you like column E to be populated once all 4 cells (A:D) have data, or is there only one column that matters? – ashleedawg Nov 24 '17 at 09:42
yes i need Value in "E12 : E21 " as 01.Nov.2017 only if A:D have data. But A:D will be automatically calculated. For next month A22 :D24 will contain data. So i need values in "E22: E24 " as 01. Dec.2017. – Deepak Nov 24 '17 at 09:51
This is why it's helpful to post sample data (representative of the actual data), both of what you have now, and what you need to have. Anyhow, with the formulas above you can get the date into the cells that have data this month. If you want to "lock the values in" at the end of the month, a line or 2 of code can copy & [Paste Values](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-pastespecial-method-excel) on the cells in E that have data. Might be a good idea to check out the tips at [ask] and [mcve]. Good luck! – ashleedawg Nov 24 '17 at 10:04
@ ashleedawg I said from cell E12:E22 i need only value as 01.Nov.2017. not as 01.Nov.2017, 01.Dec.2017. I dont need excel formula. Because my original data contain more than 400000 rows. – Deepak Nov 24 '17 at 10:09

How to insert and drag the formula till the last row in Excel VBA

2 Answers2

Modified formula: