Subtracting Dates to Last Row

[loract]

One of the things I am trying to do with a file is Subtract 10 days from a column A called "OUTDATE" and put the resulting date in Column J. This is how I am working it just now:

At the beginning of the sub I use this:

lR = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

'and then in the section where I am subtracting the dates, I use this:
 Dim i As Integer
   For i = 2 To lR
 
        Range("J" & i) = DateAdd("d", -10, Range("A" & i))
    Next i

This gives me an "Application-defined or object-defined error" and highlights the "Range" line. HOWEVER, the dates actually do work all the way to the last row. So it's almost like it does what I want it to do but then gives up. This is the last action of many that the macro does, so the next line is End Sub.

If I change the "For i" line to For i = 2 to 60 or some other number, the macro works fine as long as there are sixty columns or more (but it only gives me dates for rows 2 to 60). But if there are less than sixty it throws an error. I really need it to find the last row.

I'd appreciate any help!
Thanks!

 

[Joe4]

What is your value of lR?
What if you change this line:

Dim i as Integer

to this:

Dim i as Long

 

[loract]

What is your value of lR?
What if you change this line:

Dim i as Integer

to this:

Dim i as Long

I gave that a try with the same result. It works but then throws up the same error.

 

[loract]

I gave that a try with the same result. It works but then throws up the same error.

This is how I define the last row at the beginning of the Sub:

lR = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

 

[Joe4]

This is how I define the last row at the beginning of the Sub:

lR = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

I know, but what exactly is the value it is being set?
You can add a simple MsgBox after that line to show the value, i.e.

lR = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
MsgBox lR

 

[loract]

I know, but what exactly is the value it is being set?
You can add a simple MsgBox after that line to show the value, i.e.

lR = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
MsgBox lR

Ah. It came up with 67. Which is the correct number of rows.

 

[Joe4]

Ah. It came up with 67. Which is the correct number of rows.

Does every cell in the range A2:A67 have a valid date in it?
Any blanks, text entries, or errors anywhere in that range?

 

[loract]

Does every cell in the range A2:A67 have a valid date in it?
Any blanks, text entries, or errors anywhere in that range?

Well, the last three rows ARE blank. This is bc the very first thing I do in the macro is delete the first three rows, since the worksheet is generated by a report from our software. Would it help if I deleted the rows first and THEN defined the last row? I use lr in other parts of the macro and it doesn't seem to cause trouble.

 

[Joe4]

Maybe add a line that checks for a value in column before doing the math, i.e.

   For i = 2 To lR
        If Range("A" & i).Value > 0 Then Range("J" & i) = DateAdd("d", -10, Range("A" & i))
    Next i

 

[loract]

Maybe add a line that checks for a value in column before doing the math, i.e.

   For i = 2 To lR
        If Range("A" & i).Value > 0 Then Range("J" & i) = DateAdd("d", -10, Range("A" & i))
    Next i

That did it!!
Thank you!! I appreciate your help very much!!