Finding correct blank cell

[quattda]

I am new to VBA and am trying to learn its features for use in a project I am creating. Any help would be greatly appreciated.
I have a worksheet that contains dates in the A column and text categories in Row 1. I would like to create an VBA module that runs at startup that finds the cell that is found at the intersection of the day's date (in column A) and the first blank cell in Row 1. Can someone help me get started?

[tpoynton]

probably a better way to do it, but this should work

[vba]
Dim cell As Range
For Each cell In Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
If cell.Value = Date And cell.Offset(0, 1).Value = "" Then
cell.Offset(0, 1).Select
Exit Sub
End If
Next cell
[/vba]

hope this isnt a homework project...verify that it is not HW and someone will tell you where to put it

if you had more than 1 post, i wouldnt ask...

[quattda]

Thanks.
Actually, I am a teacher creating a project timesheet for my students to use in my drafting class.

[tpoynton]

aha - put this in ThisWorkbook

[vba]
Private Sub Workbook_Open()
Dim cell As Range
For Each cell In Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
If cell.Value = Date And cell.Offset(0, 1).Value = "" Then
cell.Offset(0, 1).Select
Exit Sub
End If
Next cell
End Sub
[/vba]

There is probably a slicker way to do this, but this should work...as long as the dates are sorted ascending, and the data are filled in from the top down.

[quattda]

I ran this in a test file but it does not quite do what I need. It found the correct date in Column A (in row 17) but I need it to find the first blank column (column J) in Row 1 and then move to the correct cell (e.g., J17). Obviously, the final cell would change as the day changes and more info is added to the columns in Row 1.

[tpoynton]

Upon closer inspection of your post, i see you are now only looking in row 1 for the blank cell? sorry about that.

Posting a sample workbook would be very helpful; you can do so by clicking 'go advanced' below, and then finding 'manage attachments'

[quattda]

Here is a sample workbook. As today is August 31st and I is the first blank column, I would like to have a module that finds and activates cell I19.

Thanks

[Bob Phillips]

[vba]

Dim mpRow As Long
Dim mpCol As Long

With ActiveSheet

mpCol = .Range("A1").End(xlToRight).Column + 1
On Error Resume Next
mpRow = Application.Match(CLng(Date), .Columns(1), 0)
On Error GoTo 0
If mpRow > 0 Then .Cells(mpRow, mpCol).Select
End With
[/vba]

[tpoynton]

see, I knew there was a way to do this without a loop, I just didnt know what it was . I've never used application.match, and probably should...

[quattda]

Thanks all for the help. It worked fine.

[mdmackillop]

An alternative

[VBA]Sub FindBalank()
Columns(1).Find(Date).Offset(0, Application.CountA(Rows(1))).Activate
End Sub
[/VBA]