CODEWITHSUNDEEP
vbaexcel
Andriy T.
Andriy T.

Reputation: 2030

Find date column

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?

Upvotes: 0

Views: 192

Answers (3)

chris neilsen
chris neilsen

Reputation: 53136

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

Upvotes: 3

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

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

Upvotes: 1

Manuel Allenspach
Manuel Allenspach

Reputation: 12745

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

Upvotes: 1

Related Questions