VBA - Invalid or unqualified reference error

Question

I'm trying to create excel template (the volume of data will be different from case to case) and it looks like this:

In every even row is "Customer" and I would like to put in every odd row "Ledger". Basically it should put "Ledger" to every odd row until there are data in column C. I have this code:

'========================================================================
' INSERTING LEDGERS for every odd row (below Customer)
'========================================================================

Sub Ledgers()

    Dim rng As Range
    Dim r As Range
    Dim LastRow As Long

    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("C5:C" & LastRow)

    For i = 1 To rng.Rows.Count
        Set r = rng.Cells(i, -2)
        If i Mod 2 = 1 Then
            r.Value = "Ledger"
        End If

    Next i

End Sub

But it gives me an error msg Invalid or unqualified reference. Could you advise me, where I have the error, please?

Many thanks!

Answer 1

If a command starts with . like .Cells it expects to be within a with statement like …

With Worksheets("MySheetName")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("C5:C" & LastRow)
End With

So you need to specify the name of a worksheet where the cells are expected to be in.

Not that it would be a good idea to use Option Explicit at the top of your module to force that every variable is declared (you missed to declare i As Long).

Your code could be reduced to …

Option Explicit 

Public Sub Ledgers()
    Dim LastRow As Long
    Dim i As Long

    With Worksheets("MySheetName") 
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        'make sure i starts with a odd number
        'here we start at row 5 and loop to the last row
        'step 2 makes it overstep the even numbers if you start with an odd i
        'so there is no need to proof for even/odd
        For i = 5 To LastRow Step 2 
            .Cells(i, "A") = "Ledger" 'In column A
            '^ this references the worksheet of the with-statement because it starts with a `.`
        Next i
    End With
End Sub