Macro to copy down gives #NAME?

[Tim Hanson]

Can someone tell me what I am doing wrong?

Thanks

Sub NumID()

    Dim rng As Range
    Dim Lastrow As Long

    With Sheets("sheet1")
        Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
    End With

    Range("C3:C" & Lastrow).FormulaR1C1 = "=IF(B2=B3,C2,C2+1)"
   
    Range("C3:C" & Lastrow).Copy
    Range("C3:C" & Lastrow).PasteSpecial xlPasteValues


End Sub

 

[Luke M]

This is the problem:

Range("C3:C" & Lastrow).FormulaR1C1 = "=IF(B2=B3,C2,C2+1)"

You stated you were giving Formula in R1C1 terms, and then gave it a A1 type nomenclature.
Should be:

Range("C3:C" & Lastrow).Formula = "=IF(B2=B3,C2,C2+1)"

or, if you want to try and get fancy and make it shorter:

Range("C3:C" & Lastrow).Formula = "=C2+(B2<>B3)"

 

[Tim Hanson]

Thank you Luke, a little confused still on when and how to use .FormulaR1C1 or just .Formula

 

[Luke M]

They are technically two different systems for referring to cells. Perhaps this article will shed some light:
http://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

If you usually use A1 style references in your workbook, I recommend using just .Formula when writing your macros, as it will make more sense to you. As you get used to dealing with relative references and R1C1, then you can use FormulaR1C1. But really, whichever one you understand more is the one you should use.

 

[Tim Hanson]

Thanks Luke for the comment, I will read the article.