PDA

View Full Version : Finding correct blank cell



quattda
08-30-2007, 06:05 PM
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
08-30-2007, 06:20 PM
probably a better way to do it, but this should work


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


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
08-30-2007, 06:44 PM
Thanks.
Actually, I am a teacher creating a project timesheet for my students to use in my drafting class.

tpoynton
08-30-2007, 06:54 PM
aha - put this in ThisWorkbook


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


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
08-30-2007, 07:09 PM
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
08-31-2007, 05:10 AM
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
08-31-2007, 05:41 AM
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
08-31-2007, 05:56 AM
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

tpoynton
08-31-2007, 06:44 AM
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
08-31-2007, 09:10 AM
Thanks all for the help. It worked fine.

mdmackillop
08-31-2007, 11:51 AM
An alternative

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