0

I am attempting to:

  1. check if the final row is blank.
  2. Check if the final non-blank row contains today's date
  3. If not, enter today's date in the first blank row, each the columns indicated by the Dims. (because there are 5 separate tables that need the date).

Does my code accomplish this? I would like it to update whenever I open the workbook, but keep the date that was input the day before. So, essentially, every day when I need to update the data, today's date will already be set in there and I can just put the data in.

Code:

Private Sub Workbook_Open() Dim D1Col As Long, D2Col As Long, D3Col As Long, D4Col As Long, D5Col As Long, rowCnt As Long D1Col = 1 D2Col = 4 D3Col = 7 D4Col = 10 D5Col = 13 endRow = Cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A If endRow.Offset(1, 0).Value = 0 Then 'Does the zero idicate "if it is blank"? If endRow.Value <> Format(Now(), "mm/dd/yyyy") Then Cells(endRow.Offset(1, 0), D1Col) = Format(Now(), "mm/dd/yyyy") Cells(endRow.Offset(1, 0), D2Col) = Format(Now(), "mm/dd/yyyy") Cells(endRow.Offset(1, 0), D3Col) = Format(Now(), "mm/dd/yyyy") Cells(endRow.Offset(1, 0), D4Col) = Format(Now(), "mm/dd/yyyy") Cells(endRow.Offset(1, 0), D5Col) = Format(Now(), "mm/dd/yyyy") End If Else: endRow.Offset(1, 0).Value = 0 End Sub

Example of Data:

Screenshot of Data

Improve this question
4
  • Does your code even compile? Commented Mar 9, 2017 at 12:52
  • 3
    Are you asking us if your code does that? What happens when you run it, does it do that!? Commented Mar 9, 2017 at 12:58
  • Honestly, I don't believe so. I wrote down a code that made sense in terms of the steps that the program should take to reach the final 'destination' but that is about all I know. I have used the offset function in excel formulas, but never in VBA and I am very much a lost child when it comes to VBA so I would appreciate any help you could offer. Commented Mar 9, 2017 at 13:01
  • 1
    A) Place your cursor on the first line of your Workbook_Open() code, then hit F8. That will start debugging your code, continue to hit F8 one line at a time and watch what your code does as it executes each line of code. Is it doing what you want? B) Instead of using .Offset(), set endRow = endRow + 1, then Cells(endRow,DxCol) = ... to eliminate the somewhat annoying (in my opinion) use of .Offset() Commented Mar 9, 2017 at 13:28

4 Answers 4

Reset to default
4

I've cleaned up and refactored to eliminate the redundant code, and made explicit all implicit ActiveSheet references:

Private Sub Workbook_Open() Const startCol As Long = 1 Const colCountToSet As Long = 5 Const skipColCount As Long = 3 Dim endRow As Long endRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row If IsDate(Cells(endRow, startCol)) Then If Int(CDate(ActiveSheet.Cells(endRow, startCol))) <> Date Then endRow = endRow + 1 Dim curCol As Long curCol = startCol Dim counter As Integer For counter = startCol To colCountToSet ActiveSheet.Cells(endRow, curCol) = Date curCol = curCol + skipColCount Next End If End If End Sub

Here's what was done and why:

 Const startCol As Long = 1 Const colCountToSet As Long = 5 Const skipColCount As Long = 3

If you ever need to add or remove a set of columns, adjust colCountToSet and your code continues to work.

If you add another column to each set or add space between data sets, adjust skipColCount and your code continues to work.

If you insert a new col A, adjust startCol

Dim todaysDate As String todaysDate = Format(Now(), "mm/dd/yyyy")

There's no sense in calling the Format() function multiple times, you're only interested in the date, and if someone happens to open the workbook moments before midnight, you could possibly get different dates on the same row. 

 If IsDate(ActiveSheet.Cells(endRow, startCol)) Then

I've fixed up this bit thanks to a suggestion from @Comintern. First ensure your last row contains a date. If, for some reason, someone has entered a non-date value at the bottom, this will skip overwriting it. 

 If Int(CDate(ActiveSheet.Cells(endRow, startCol))) <> Date Then

The Date function returns a date (as an integer) without the time, so compare that to what's in the last row.

 If Format(ActiveSheet.Cells(endRow, startCol), "mm/dd/yyyy") <> todaysDate Then

If the last row is empty, it won't match. If it's yesterday's date, it won't match. Either case falls into the If statement. If it's today's date, it will match and it will skip the If statement. You have to format the date in the column to exactly match the format you're using, since the display formatting of the cell could return a different looking string than what you're testing for. 

 endRow = endRow + 1

Get rid of the .Offset(). Especially since you're not using a Range object.

 For counter = startCol To colCountToSet ActiveSheet.Cells(endRow, curCol) = Date curCol = curCol + skipColCount Next

A nice, simple little loop that will set each column's date without having to adjust anything other than a CONST or two at the very top of the code should your formatting ever change. It sets the date to the system returned Date integer.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you so much for providing a step by step explanation for the code - it is very much appreciated and I definitely understand the reason you took each step now.
@Eva - I'm glad that helped. I've updated a bit based on a tip from Comintern. Also, be sure you read my comment on the OP about debugging the code so you'll know in the future whether your code is working or not, and how to find out where it's breaking. The debugger is your friend!
3

Try something like the code below :

Dim D1Col As Long, D2Col As Long, D3Col As Long, D4Col As Long, D5Col As Long, rowCnt As Long Dim endRow As Long D1Col = 1 D2Col = 4 D3Col = 7 D4Col = 10 D5Col = 13 With Worksheets("Sheet4") ' <-- define which sheet to perform the tests below endRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' <-- set 'endRow' to column A If .Range("A" & endRow).Offset(1, 0).Value = 0 Or .Range("A" & endRow).Offset(1, 0).Value = "" Then ' check is blank (maybe also zero, not sure whatthe PO wants) If .Range("A" & endRow).Value <> Date Then .Cells(endRow + 1, D1Col) = Date .Cells(endRow + 1, D2Col) = Date .Cells(endRow + 1, D3Col) = Date .Cells(endRow + 1, D4Col) = Date .Cells(endRow + 1, D5Col) = Date End If Else .Range("A" & endRow).Offset(1, 0).Value = 0 End If End With
Improve this answer

2 Comments

I think @OP also wants the If statement to check if the cell is blank, not that .Value = 0 - perhaps use IsEmpty() in the If statement instead, in conjunction with .Value = "" to check for null strings. The Else statement should probably also return a blank cell as opposed to 0
@Jordan I wasn't sure what he wanted, but your suggestion seems more reasonable.
1

I don't have the reputation for a comment, but I think you should train a bit with the basics of VBA, here are a few tips:

endRow = Range("A1").End(xlDown).Row '<--| really set 'endRow' to column A in active sheet! 
If Cells(endRow, D1Col).Value = "" Then 'if cell in active sheet is blank... 
If Cells(endRow, D1Col).Value <> Date Then 'if active sheet cell value is today's date... 
Cells(endRow + 1, D1Col).Value = Date 'set the cell in the next row to today's date

Good luck

Improve this answer

Comments

0

Your code won't work, code below will return error..

endRow = Cells(Rows.Count, 1).End(xlUp).Row '<--| set 'endRow' to column A If endRow.Offset(1, 0).Value = 0 Then 'Does the zero idicate "if it is blank"?

The reason is endRow is assigned with Row property that return an numeric value not a Range object, so you cannot access the Offset object from endRow variabel. If you want to access lastNonBlank or firstBlank row use the code below

Public Sub LastRow() Dim lastNonBlankRow As Range Dim firstBlankRow As Range Set lastNonBlankRow = Range("A1").End(xlDown) Set firstBlankRow = Range("A1").End(xlDown).Offset(1, 0) MsgBox lastNonBlankRow.Address MsgBox firstBlankRow.Address End Sub
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.