Help! Need help to correct and undetermined Range to avoid #N/A's

[liferg]

I had recorded a macro and the given range was ("P2:P3247"), however this number can vary from day to day, higher or lower. If the number is lower then it fills in the #N/A's, and if it goes over then I lose data for the remainder of the macro. I have extended it out to 5000 just to make sure I get all of the data if it goes over, but then I'm stuck with a lot more of the #N/A's. I attempted to use a code from a google search and now I can't get it back to the way it was. Can someone please provide me with some assistance on this? I have a report that needs to be done by 2:00.

 LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
    Range ("P1:P" & LastRow)
    With Range("P2")
        .AutoFill Destination:=Range("P2:P" & InLastRow)
    End With
    With Range("P2:P5000")
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With

 

[VoG]

Try

LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
    With Range("P2")
        .AutoFill Destination:=Range("P2:P" & LastRow)
    End With
    With Range("P2:P" & LastRow)
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

 

[liferg]

I am getting a Run-time error '1004': AutoFill method of Range class Failed

LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
    With Range("P2")
        .AutoFill Destination:=Range("P2:P" & LastRow)
    End With
    With Range("P2:P" & LastRow)
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

 

[JoeMo]

Try:

    With Range("P2:P" & LastRow)
        .AutoFill
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

 

[liferg]

I get a Compile Error: Argument not optional

    Range("P2") = "=TODAY()-RC[-1]"
    LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
    With Range("P2:P" & LastRow)
        .AutoFill
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

 

[JoeMo]

If P2 contains a formula like:

Range("P2") = "=TODAY()-RC[-1]"
you can't autofill using that cell as the source. Can you explain exactly what you are trying to accomplish?
</pre>

 

[liferg]

Interesting...because when you record and then do the autofill it uses the P2 cell. What I'm trying to do is to copy the formula that is in cell P2 all the way down until the data ends. The problem is the data will not stop at the same spot every time this macro is done. So I am trying to find a code that will know when to stop each time it's ran without having to go in and manually change the numbers so that no data is lost or contains the #N/A's.

 

[JoeMo]

Then in the code I posted, change .Autofill to .FillDown

 

[liferg]

Thank you Joe, the code is not getting any errors, however it's only filling the code down to row 3. I need it to go all the way down until the very last row of data. Is this possible?

 

[JoeMo]

Thank you Joe, the code is not getting any errors, however it's only filling the code down to row 3. I need it to go all the way down until the very last row of data. Is this possible?

Have you stepped through the code to see what tthe value of LastRow is when it only fills to row 3? Most likely it is 3.