Filling date on specific range inside a table

Question

I have a table where i would like to insert a date as shown in image. It will copy the date to some continuos range. The program must find the range and then insert date using inputbox. I used the code below. The problem is it is not selecting the range inside a table. How to solve this. Help me

Sub FillFirstDay()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim table As ListObject
Dim dat As Date

Set ws = Sheets("Raw Data")
dat = Application.InputBox(prompt:="Enter the received date of the current Month", Title:="Date", Default:=Format(Date, "dd/mm/yyyy"), Type:=2)

If dat = False Then
MsgBox "Enter a Date", , "Date"
Exit Sub
End If

With ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    firstRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
    Set rng = Range(.Range("C" & firstRow), .Range("C" & LastRow))
End With

If firstRow >= LastRow Then Exit Sub

With rng
    .Value = dat
    .NumberFormat = "m/d/yyyy"
    .NumberFormat = "[$-409]dd-mmm-yy;@"
End With
End Sub

score 1 · Accepted Answer · answered Feb 09 '18 at 13:26

1

This line here is the problem:

firstRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1

The .End(xlUp) code catches the bottom of the table on its way up. You have to do it twice to move up to the bottom of where the data is. This modified line will fix your issue:

firstrow = .Range("C" & .Rows.Count).End(xlUp).End(xlUp).Row + 1

answered Feb 09 '18 at 13:26

Greg Viers

3,473
3
18
36

Thank you very much Greg. Works perfectly. – Deepak Feb 09 '18 at 13:41

score 1 · Answer 2 · answered Feb 09 '18 at 13:53

What about this?

Sub FillFirstDay()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim dat As Date

Set ws = Sheets("Raw Data")

dat = Application.InputBox(prompt:="Enter the received date of the current Month", Title:="Date", Default:=Format(Date, "dd/mm/yyyy"), Type:=2)

If dat = False Then
    MsgBox "Enter a Date", , "Date"
    Exit Sub
End If

Set tbl = ws.ListObjects(1)
On Error Resume Next
Set rng = tbl.DataBodyRange.Columns(3).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    With rng
        .Value = dat
        .NumberFormat = "m/d/yyyy"
        .NumberFormat = "[$-409]dd-mmm-yy;@"
    End With
Else
    MsgBox "Date column is already filled.", vbExclamation
End If
End Sub

score 1 · Answer 3 · answered Feb 09 '18 at 14:03

since you have a Table object, use it!

Option Explicit

Sub FillFirstDay()
    Dim aRow As Long, cRow As Long

    With Sheets("Raw Data").ListObjects("Table01").DataBodyRange 'reference ytour table object (change "Table01" to your actual table name)
        aRow = WorksheetFunction.CountA(.Columns(1))
        cRow = WorksheetFunction.CountA(.Columns(3))
        If cRow < aRow Then 'check for empty cells in referenced table 3rd column comparing to 1st one
            Dim dat As Date
            dat = Application.InputBox(prompt:="Enter the received date of the current Month", Title:="Date", Default:=Format(Date, "dd/mm/yyyy"), Type:=2)
            If dat = False Then 'check for a valid Date
                MsgBox "you must enter a Date", , "Date"
                Exit Sub
            Else
                With .Columns(3).Offset(cRow).Resize(aRow - cRow) 'select referenced table 3rd column cells from first empty one down to last 1st column not empty row
                    .Value = dat
                    .NumberFormat = "m/d/yyyy"
                    .NumberFormat = "[$-409]dd-mmm-yy;@"
                End With
            End If
        End If
    End With
End Sub

Filling date on specific range inside a table

3 Answers3