Find date column

Question

I am new in VBA and I am trying to write a macro that will find a column that contains dates in my data and select a column next to it.

Date is something like:

Date    output
01/02/2011  200
02/02/2011  200
03/02/2011  200

I have tried:

Sub Macro5()
    With ActiveSheet.Cells
        .Find(what:=vbDate).Select
    End With

    ActiveCell.Offset(0, 1).Select
    ActiveCell.EntireColumn.Select
End Sub

but it doesn't work as expected. Can anyone give me a solution, please?

vbDate = 7 so in your .Find statement you are searching for the number 7. Just search for the column label ('Date') and you might limit your search to the first row. — Ron Rosenfeld, Apr 29 '14 at 10:28
Are you saying, you have no idea ahead of time where on the sheet the dates could be? So you have to search for dates, any dates? — Jean-François Corbett, Apr 29 '14 at 10:37
And you can search search for a _format_ (at least in modern versions of excel) — chris neilsen, Apr 29 '14 at 10:40
@RonRosenfeld, the problem is that the date column in my tables isn't allways calls 'Date'. — Andriy T., Apr 29 '14 at 10:41
@Jean-FrançoisCorbett, at list first cell that contains date in my worksheet. — Andriy T., Apr 29 '14 at 10:42
@chrisneilsen: That is a valid answer ;) You may want to add that as an answer :) — Siddharth Rout, Apr 29 '14 at 10:51
You can search for a format using `Application.FindFormat.NumberFormat = "something"`, in this case `dd/mm/yyyy` — Dan Wagner, Apr 29 '14 at 10:56
`Thank's for comments! Not to you @SiddharthRout – AndriyTkach 1 min ago` - 1 For being sarcastic — Siddharth Rout, Apr 29 '14 at 11:22

score 3 · Accepted Answer · answered Apr 29 '14 at 10:55

3

Heres a demo of searching for a Format

Sub Demo()
    Dim rng As Range
    Dim rngDate As Range

    ' Set reference to search range by whatever means
    Set rng = ActiveSheet.UsedRange

    ' Setup search
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = "m/d/yyyy"

    ' Do search
    Set rngDate = rng.Find(What:="*", SearchFormat:=True)

    ' report result
    If Not rngDate Is Nothing Then
        MsgBox "Found a Date formated cell in column " & rngDate.Column
    End If
End Sub

answered Apr 29 '14 at 10:55

chris neilsen

52,446
10
84
123

+ 1 Nicely done. Much better than looping through cells. – Siddharth Rout Apr 29 '14 at 10:55
@sid taking my time to write a (I hope) decent answer pays off! – chris neilsen Apr 29 '14 at 10:57
IMHO, Chris, that is the best way and pays off in the long run ;) – Siddharth Rout Apr 29 '14 at 10:58
@sid like minds... IMNSHO I wish more pepole would think like this – chris neilsen Apr 29 '14 at 11:00
If anyone has doubts, show them [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) :P – Siddharth Rout Apr 29 '14 at 11:01

score 1 · Answer 2 · answered Apr 29 '14 at 10:49

The code below searches for a date in your active sheet. As soon as it finds one, it selects the column next to it and stops.

Dim rng As Range
For Each rng In ActiveSheet.UsedRange
    If IsDate(rng.Value) Then
        rng.Offset(0, 1).EntireColumn.Select
        Exit Sub
    End If
Next

score 1 · Answer 3 · answered Apr 29 '14 at 10:53

Here's an example that searches row 2 for cells formatted as a date (assuming any date contains at least the day i.e the string d):

Dim i As Long
With Sheet1.Range("2:2")    ' or wherever you want to search
    For i = 1 To .Columns.Count
        With .Cells(1, i)
            If InStr(.NumberFormat, "d") <> 0 Then
                .Offset(0, 1).EntireColumn.Select
                Exit For
            End If
        End With
    Next i
End With

Find date column

3 Answers3