Copying formula to a range of cells and converting it to a value

excel, vba

Solution

You have already got two excellent answers and hence this is not an answer but an enhancement to your code.

You don't need to declare the row variable as `Double`. `Long` is good enough. You may want to read up on Data type HERE

Your code can be reduced to this

Code

BTW, Your question says `D1` but your code is taking `D2` into consideration. So I am going with `D2`. If it is `D1` then replace `D2` by `D1` below.

Sub Sample()
    Dim lrow As Long

    With Sheets("Sheet2")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("D2:D" & lrow).Formula = .Range("D2").Formula
        .Range("E2:E" & lrow).Formula = .Range("E2").Formula
        .Range("F2:F" & lrow).Formula = .Range("F2").Formula
        .Range("G2:G" & lrow).Formula = .Range("G2").Formula

        .Range("D2:G" & lrow).Value = .Range("D2:G" & lrow).Value
    End With
End Sub

Problem

I am trying to copy the formula from D1 and paste it till last row(26446) value may increase ``` Dim lrow As Double lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row Range("D2").Copy Destination:=Range("D2:D" & lrow) Range("D2" & "lrow").Copy 'THIS LINE SHOWS 1004 error Range("D2:D" & "lrow").PasteSpecial Paste:=xlPasteValues ``` How to solve this

Original source