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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.