CODEWITHSUNDEEP
vbaexcelexcel-2010
Deepak
Deepak

Reputation: 473

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

enter image description here 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

Upvotes: 0

Views: 1509

Answers (2)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

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.

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

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:

example formula

Upvotes: 0

Related Questions