VBA showed the error "Application-defined or object-defined error"

Question

I want to collect daily summary data from half hour time interval data. However, when I tried to run the code, VBA showed the error "Application-defined or object-defined error".

Sub IDSelect()
    Dim j As Integer, k As Integer, L As Integer, difference As Integer

    Application.ScreenUpdating = False

    j = 2
    k = 49
    difference = 1

    Sheets("sheet1").Select

    Do Until Cells(j, 5) = ""        
        Range(Cells(j, 5), Cells(j + 1, 12)).Select
        Selection.Copy

        Sheets("sheet2").Select

        L = ActiveSheet.UsedRange.Rows.Count
        Cells(L, 1).Select
        ActiveSheet.Paste

        Range(Cells(L, 7)).Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=SUM(Sheet1!R[j - difference]C[4]:R[k - difference]C[4])"

        Range(Cells(L, 8)).Select
        ActiveCell.FormulaR1C1 = "=SUM(Sheet1!R[j - difference]C[4]:R[k - difference]C[4])"

        k = k + 48
        j = j + 48
        d = d + 1

        Sheets("sheet1").Select
    Loop

    MsgBox "Finished", vbInformation, "Info"
    Application.ScreenUpdating = True
End Sub

As @DarrenBartrup-Cook eludes to, there is almost no value add using `Integer` in VBA. You might also want to read up about using `.Select` as this is almost completely unnecessary in VBA. — Dean, Jul 23 '19 at 07:07
I would recommend reading How to avoid using Select in Excel VBA (https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Malan Kriel, Jul 23 '19 at 07:11
I tried to replacing Integer with Long, and the same error is still there. — Frank, Jul 23 '19 at 07:12
Then tell us what line throws the error @Frank. It's difficult to help debugging the code without sufficient information — Tim Stack, Jul 23 '19 at 07:18
@ Tim, the error came after "Cells(L, 1).Select", which started at "ActiveSheet.Paste". — Frank, Jul 23 '19 at 07:30
I am assuming your `L` variable is populated with a nonsense row number. This could be a number below 0, or a number larger than the max nr. of rows in your Excel version. — Tim Stack, Jul 23 '19 at 07:32
The error was in the following two lines: `ActiveCell.FormulaR1C1` (you cannot feed the VBA variables into the formula. You need to calculate the row/column references). Also, `Range(Cells(L, 7)).Select` gave me an error which I avoid in my answer below. — Malan Kriel, Jul 23 '19 at 07:40
@Frank use `Debug.Print L` to return the value of `L` to your immediate window to verify what @TimStack mentions — Dean, Jul 23 '19 at 07:41
@GSerg that's a false duplicate mark. Error may be part of the code but is not the reason for OP's post — Tim Stack, Jul 23 '19 at 07:51
@TimStack The only place where `L` is assigned is `= ActiveSheet.UsedRange.Rows.Count`. That expression will always return a number from 1 to max number of rows on a sheet, so it will always be a valid argument for `Cells(row, col)`. The only issue that `L` could have caused is overflowing the [Integer range](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/integer-data-type), but then the error would be [different](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/overflow-error-6). — GSerg, Jul 23 '19 at 07:52
@GSerg I agree, but OP has changed the Integer variables to Long, yet the code breaks at `Cells(L, 1).Select`. I think we're missing some information here — Tim Stack, Jul 23 '19 at 07:54
@TimStack Changing to `Long` was correct, but it is not the source of the immediate problem because the OP evidently does not have more that 32767 rows in the `UsedRange`. The `Range(Cells(L, 7))` fails not because of `L`, it fails because it's invalid to pass a single Range object to the Range property. When used with just one argument, it should be the address of the range (e.g. `Range("A1")`), and if you want to pass cells, you must pass two (`Range(Cells(..), Cells(..))`). To fix that line, you replace the `Range(Cells(L, 7))` with `Cells(L, 7)` because it is already a Range. — GSerg, Jul 23 '19 at 07:59
@Malan Kriel, thank you so much! It works exactly what I want right now! — Frank, Jul 23 '19 at 23:00
@ all, thank you so much for your help. While can someone explain to me the reason why I should avoid "select" here? — Frank, Jul 23 '19 at 23:30
@Frank I'm not a programmer by trade, but the way I think about it is using `.Select`, `.Copy`, `.Paste`, etc we let the computer operate like a human which is extremely inefficient. It's much better to `Set Objects` and work with them instead of selecting cells, etc. Could you please accept/upvote the answer below if you are happy? — Malan Kriel, Jul 24 '19 at 07:40
I have accepted your answer! Thank you so much for explaining that! That's really helpful! — Frank, Jul 25 '19 at 06:50

Malan Kriel · Accepted Answer · 2019-07-23T07:37:54.457

The below code avoids using .Select (Note that .Copy and .Paste should also be avoided in VBA code):

Sub IDSelect()
Dim j As Integer, k As Integer, L As Integer, difference As Integer
Dim WS1 As Worksheet, WS2 As Worksheet

Set WS1 = ThisWorkbook.Sheets("sheet1")
Set WS2 = ThisWorkbook.Sheets("sheet2")

    Application.ScreenUpdating = False

          j = 2
          k = 49
          difference = 1

        Do Until WS1.Cells(j, 5) = ""

          Range(WS1.Cells(j, 5), WS1.Cells(j + 1, 12)).Copy

          With WS2
                L = .UsedRange.Rows.Count
                .Cells(L, 1).PasteSpecial
                Application.CutCopyMode = False
                .Cells(L, 7).FormulaR1C1 = "=SUM(Sheet1!R[" & j - difference & "]C[4]:R[" & k - difference & "]C[4])" ' need to add & " ...
                .Cells(L, 8).FormulaR1C1 = "=SUM(Sheet1!R[" & j - difference & "]C[4]:R[" & k - difference & "]C[4])"
          End With

        k = k + 48
        j = j + 48
        d = d + 1

        Loop

        MsgBox "Finished", vbInformation, "Info"
        Application.ScreenUpdating = True

End Sub

@Frank I have tested the code above and it works on my side. — Malan Kriel, Jul 23 '19 at 07:42

VBA showed the error "Application-defined or object-defined error"

1 Answers1