How to solve object defined error in Visual Basic?

Question

I have run into some problems in VBA. The code works fine if actual_row is greater than 8 and in the and adds "X"-s to the defined range. However when the variable is equal to 8 (when I only want to add the X-s to the range) I get object defined error. Has anybody encountered such problem like this?

    If sf_actual_row > 8 Then
        Worksheets("sf_promo_Central").Range("A7:N7").Copy
        Worksheets("sf_promo_Central").Range("A7:N" & sf_actual_row - 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
               
        
        Let SourceRange = "N" & 7 & ":" & Split(Cells(1, total_central_location).Address, "$")(1) & 7
        Let fillRange = "N" & 7 & ":" & Split(Cells(1, total_central_location).Address, "$")(1) & sf_actual_row - 1

        Worksheets("sf_promo_Central").Range(SourceRange).AutoFill Destination:=Worksheets("sf_promo_Central").Range(fillRange), Type:=xlFillDefault
        
        Worksheets("sf_promo_Central").Activate
        Worksheets("sf_promo_Central").Range(Cells(sf_actual_row, 1), Cells(sf_actual_row, total_central_location)).Value = "X"
        
        
    ElseIf sf_actual_row = 8 Then
        Worksheets("sf_promo_Central").Range(Cells(sf_actual_row, 1), Cells(sf_actual_row, total_central_location)).Value = "X"
    Else: End If

Side notes: `Let` is deprecated. Don't work with addresses, use `Cells` and the column index. A red flag: unqualified `Cells` calls, in `Worksheets("sf_promo_Central").Range(Cells(sf_actual_row, 1), Cells(sf_actual_row, total_central_location))` and similar, see [this question](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) and [this question](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet). — BigBen, Mar 17 '21 at 16:38

score 0 · Accepted Answer · answered Mar 17 '21 at 16:41

Worksheets("sf_promo_Central").Range(Cells(sf_actual_row, 1), Cells(sf_actual_row, total_central_location)).Value = "X"

should be more like

With Worksheets("sf_promo_Central")
    .Range(.Cells(sf_actual_row, 1), .Cells(sf_actual_row, total_central_location)).Value = "X"
end with

Cells also need to be scoped to the worksheet

How to solve object defined error in Visual Basic?

1 Answers1