0

I wrote a VB macro in excel to set a "Autotest" worksheet active and activate the cell with exact match "Date". Then, I change every date underneath this cell to advance the year value ahead 4 years. I have a lot of data sets, so I wanted this to run no regardless of format of the work books. I know there will always be an "Autotest" sheet and I know there will always be a "Date" cell above the list of dates. This code works for most of the data sets but I keep getting this error, the debug points to the lastrow statement but I can't determine the issue. Thanks in advance.

Sub FourYrsAhead()

    ' FourYrsAhead Macro
    ' This macro will forward all of the dates under the "Dates" column in the Autotest sheet forward 4 years.

'Sets variable lastRow as an integer
Dim lastRow As Integer
'Sets theSheetImWorkingOn as a worksheet variable
Dim theSheetImWorkingOn As Worksheet
'Sets theColumnNumberForTheDates as an integer
Dim theColumnNumberForTheDates As Integer

'Activates the Autotest worksheet as the active sheet
Worksheets("Autotest").Activate

'Finds the exact string 'Date' and makes it the active cell
Cells.Find(What:="Date", LookAt:=xlWhole).Activate

'Sets the column number to the current active call value
theColumnNumberForTheDates = ActiveCell.Column

'Sets the sheet that will be worked on
Set theSheetImWorkingOn = Sheets("Autotest")

'Sets the last row variable
lastRow = theSheetImWorkingOn.Cells(1000000, theColumnNumberForTheDates).End(xlUp).Row

'For loop that starts as the active cell row plus 1 and loops down each row
For x = ActiveCell.Row + 1 To lastRow

    'Changes the yyyy value in each row ahead 4 years
    theSheetImWorkingOn.Cells(x, theColumnNumberForTheDates) = DateAdd("yyyy", 4, theSheetImWorkingOn.Cells(x, theColumnNumberForTheDates))

'Moves to the next row
Next x

End Sub
RGA
  • 2,577
  • 20
  • 38
Jordan Wood
  • 11
  • 1
  • Error 1004 always means a variable doesn't exist or is undefined. When you're in debug mode, hover over your variables. I bet you'll find that `theColumnNumberForTheDates` is not a valid reference. (It's likely pointing to a different sheet...See this thread: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Tim Jul 25 '16 at 13:28
  • @Tim When I hover over the theColumnNumberForTheDates, it has a defined value of "4" which is correct as the 'Date' cell is located in the 4th column – Jordan Wood Jul 25 '16 at 13:35
  • @PhilippeGrondier I got the piece of code from a tutorial online, i figured the dataset would never reach that number and the code seems to work with most of the datasets i have so idk why its not work here. – Jordan Wood Jul 25 '16 at 13:39
  • Well, I bet wrong. :( It may be related to @RGA's answer. Maybe instead of selecting the millionth row, just let Excel figure it out with `.Cells(.Rows.Count, theColumnNumberForTheDates).End(xlUp).row`. – Tim Jul 25 '16 at 13:41
  • 1
    I get that **Date** may be in any column but are you also saying it might not be in the first row? –  Jul 25 '16 at 14:02

1 Answers1

0

Depending on which version of excel you are running, it is possible that you are exceeding the Row limit of excel. In 2003 and earlier, sheets are limited to 65536 rows, so calling the 1,000,000th row in Cells will cause an error. If you are running 2007 or later, however, this shouldn't be the issue.

Using YourSheet.Rows.Count instead of 1000000 will avoid Workbook type causing this error while still ensuring all data is covered

RGA
  • 2,577
  • 20
  • 38
  • Thanks, this is it. I'm running 2016 Excel but the files that are having the issue is Type: Microsoft Excel 97-2003. I'll have to resave them as a new excel sheet. Thanks! – Jordan Wood Jul 25 '16 at 13:41
  • @JordanWood Alternatively, just use a smaller value than 1 million (perhaps 65535?) – RGA Jul 25 '16 at 13:41
  • Thanks, that works too. My company has updated excel throughout though so its properly best to update these to .xlsx anyways. – Jordan Wood Jul 28 '16 at 16:07