Not Pasting to LastRow

[Guzzlr]

Hello
I have:

Dim LastRow As Range
Range("F6").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
Range("F6").Select
Selection.Copy
Range("F7" & LastRow).Select
Selection.PasteSpecial

My LastRow is working correctly. I am trying to copy the formula in F6 (which is working correctly) and pasting in F7 down to Last Row.
However, it is only pasting in the formula in F7 only and not all the way down to Last Row

What am I missing?

Thank you

 

[Anthony47]

Unfortunately you don't show us how LastRow is calculated, but I guess it is a "Long", not a Range, and it contains the number of the last used row, and thus the last line that should receive the new formula.
If this assumption is correct then all your code can be written as

Range("F7:F" & LastRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

If LastRow is not a Long but a range then tell us how you set it and we'll adjust the code

 

[awoohaw]

I think this:

Range("F7" & LastRow).Select

should be this:

Range("F7:" & LastRow).Select

missing a colon maybe?

although this could be alittle more succinct:
replace:

ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

with

Range("F6:" & Lastcell).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

and the rest is not needed.

but to reiterate what the prior post said. If you don't give all the components of the calculation you have then we can't be certain we are really finding out what is wrong.

 

[awoohaw]

Here is a sub that i think does what you want, not sure if your last cell calculation is similar, but use what part of this you like if you find it helpful.

Sub mmmmyyyyy()
Dim LastCellInColumn As Variant
LastRowLeftColumn = Range("F6").Offset(0, -1).End(xlDown).End(xlDown).End(xlUp).Row
Range("F6:F" & LastRowLeftColumn).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
End Sub

 

[Alex Blakenburg]

I would not have picked up that you had declared LastRow as a range if @awoohaw hadn't mentioned it.
Unfortunately the code he provided won't work.
If LastRow is indeed a range then the code would look something like this:

Range("F6:F" & LastRow.Row).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

(I would also have preferred to use LastCell in that case)

If LastRow is either an integer or long then you should go with @Anthony47's post #2.
As both the others have pointed out you are not showing where and how LastRow is getting a value or range.

 

[awoohaw]

I would not have picked up that you had declared LastRow as a range if @awoohaw hadn't mentioned it.
Unfortunately the code he provided won't work.
If LastRow is indeed a range then the code would look something like this:

Range("F6:F" & LastRow.Row).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"

(I would also have preferred to use LastCell in that case)

If LastRow is either an integer or long then you should go with @Anthony47's post #2.
As both the others have pointed out you are not showing where and how LastRow is getting a value or range.

That is strange, in Post#4 I even used "Option Explicit", and I see now the variable is not declared. (I must have changed the name when writing it).
What I posted in post #3 were guesses assuming that "lastrow" was an address or row number and not really a range.
(As I said, no one really knows what the intention of "lastrow" is because there is no information on its calculation.)

Suggested code in Post #4 should be:

Sub mmmmyyyy()
Dim LastRowLeftColumn As Variant
LastRowLeftColumn = Range("F6").Offset(0, -1).End(xlDown).End(xlDown).End(xlUp).Row
Range("F6:F" & LastRowLeftColumn).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
End Sub

 

[Guzzlr]

Hello, and thanks for all the help.
I noticed yesterday that I have my LastRow as a Range, instead of Long...which is why I probably am having problems. I am away from my computer now, but when I get back, I intend to change LastRow to Long, instead of Range...
Sorry

 

[awoohaw]

Hello, and thanks for all the help.
I noticed yesterday that I have my LastRow as a Range, instead of Long...which is why I probably am having problems. I am away from my computer now, but when I get back, I intend to change LastRow to Long, instead of Range...
Sorry

I think you still need a colon and a column letter in that statement. please look at some of the other solutions as well. Best wishes.

 

[Guzzlr]

I think you still need a colon and a column letter in that statement. please look at some of the other solutions as well. Best wishes.

Hello, I placed a colon as suggested, and I got the below error:

Below is my code for Last Row:

Dim LastRow As Range
Set LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
MsgBox "The Last Cell with Data Is In Row:  " & LastRow.Row

When I tried to change Dim LastRow as Range to Long, I got an Object error.

Thanks for the help

 

[Alex Blakenburg]

Your find statement is returning a cell and you are trying to put it into a variable that will only accept a number (long).
It doesn't matter which way you do it but you need to be consistent.

Sub UsingRangeObject()

    Dim LastCell As Range
    Set LastCell = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

    Range("F7:F" & LastCell.Row).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
 
    MsgBox "The Last Cell with Data Is In Row:  " & LastCell.Row

End Sub

Sub UsingRowAsLong()

    Dim LastRow As Long
    'Note: Line does not start with Set
    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("F7:F" & LastRow).FormulaR1C1 = "=TEXT(RC[-1],""mmmm, yyyy"")"
 
    MsgBox "The Last Cell with Data Is In Row:  " & LastRow
End Sub