Object Required Error on Excel VBA when getting info from another sheet

Question

I'm creating some code to better keep track of expenses. One of the macros is meant to load fixed expenses from another worksheet, only when the amounts are not empty and, in the case of credits, only if there's still pending payments.

My code is as follows:

Sub CargarFijos()
ActiveSheet.Range("J4").Select

If Not ActiveSheet.Previous Is Nothing Then
    If Not ActiveSheet.Previous.Range("C12") Is Nothing Then
        If Not IsEmpty(ActiveSheet.Previous.Range("C12")) Then
            ActiveCell.Text = "Tarjeta de Credito"
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Value = ActiveSheet.Previous.Range("C12").Value
            ActiveCell.Offset(1, -1).Select
        End If
    End If
End If

If Not IsEmpty(ActiveCell) Then
    ActiveCell.Offset(1, 0).Select
End If

If Not Worksheets("Fijos") Is Nothing Then
    For Each c In Worksheets("Fijos").Range("A2:A40").Cells
        If Not c Is Nothing Then
            If Not IsEmpty(c.Offset(0, 1)) And Not c.Offset(0, 1) Is Nothing Then
                If IsEmpty(c.Offset(0, 2)) And Not c.Offset(0, 2) Is Nothing Then
                    ActiveCell.Text = c.Text
                    ActiveCell.Offset(0, 1).Value = c.Offset(0, 1).Value
                    ActiveCell.Offset(1, 0).Select
                ElseIf Not c.Offset(0, 2) Is Nothing And c.Offset(0, 2).Value > 0 Then
                    ActiveCell.Text = c.Text
                    ActiveCell.Offset(0, 1).Value = c.Offset(0, 1).Value
                    c.Offset(0, 2).Value = c.Offset(0, 2).Value - 1
                    ActiveCell.Offset(1, 0).Select
                End If
            End If
        End If
    Next
End If

End Sub

My worksheet "Fijos" is where I have my fixed expenses. On column A I have the descriptions, on column B I have the amount to be payed, and on column C I have the pending payments.

The idea is that I run along the A column, check the B and C column and, if there's an amount to pay on column B and pending payments (or empty) on C, I add the amount of B on my ActiveSheet.

On my ActiveSheet, column J is the description of the expenses and column K is the amount.

Whenever I execute the Macro, it says "Object Required" but doesn't say which line the error occurred at.

Any ideas? I only started trying out VBA a few days ago and it's probably a newbie mistake.

There is nothing I see here that a formula can't do. It looks like it will be much easier to link the workbooks using data links (instead of using VBA) and writing formulas (such as IF's and ISBLANK's) — ahinkle, Jun 15 '16 at 01:13
That could be a solution, but I wanted to use VBA mostly because the amount of fixed expenses can change. For instance, if I finished paying the semester at college, I won't need to load it again. Also, if I do a VLookUp with IFs and such, after a whole year of historical expenses controls, that many VLookUps and IFs will make the loading of the excel slower — KeOt777, Jun 15 '16 at 01:43
First of all, when you get an error like that the line highlighted in Yellow is the one causing the error. Secondly, the `Text` property is read only (as the Help will tell you) use `Value` instead. Thirdly, you should [avoid using Select](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Jun 15 '16 at 01:58

score 1 · Accepted Answer · answered Jun 15 '16 at 06:15

This line throw an error

ActiveCell.Text = "Tarjeta de Credito"

The text property is read only. It returns the display text of the cell not the cells value.
Use:

ActiveCell.Value = "Tarjeta de Credito"

I refactored your code removing any conditions that couldn't fail and combining the other if statements when possible.

Sub CargarFijos()
    ActiveSheet.Range("J4").Activate

    If Not IsEmpty(ActiveSheet.Previous.Range("C12")) Then
        ActiveCell.Value = "Tarjeta de Credito"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = ActiveSheet.Previous.Range("C12").Value
        ActiveCell.Offset(1, -1).Select
    End If

    If Not IsEmpty(ActiveCell) Then ActiveCell.Offset(1, 0).Select

    For Each c In Worksheets("Fijos").Range("A2:A40").Cells
        If Not IsEmpty(c.Offset(0, 1)) And IsEmpty(c.Offset(0, 2)) Then
            ActiveCell.Value = c.Text
            ActiveCell.Offset(0, 1).Value = c.Offset(0, 1).Value
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Value = c.Text
            ActiveCell.Offset(0, 1).Value = c.Offset(0, 1).Value
            c.Offset(0, 2).Value = c.Offset(0, 2).Value - 1
            ActiveCell.Offset(1, 0).Select
        End If
    Next

End Sub

Thank you, you refactoring helped me. I had no idea about the Text property. — KeOt777, Jun 15 '16 at 06:46

score 0 · Answer 2 · 2016-06-15T12:42:59.797

Nothing is wrong with your code

If Not Worksheets("Fijos") Is Nothing Then

Worksheets("Fijos") cannot = Nothing because when you reference an non-existing member of a collection Error #9 Subscript out of range is thrown.

c.Offset(0, 1) Is Nothing

c.Offset(0, x) will never = Nothing either.

if x > MaxIndexValue: the overflow error is raised
if x < 0: the Application-defined or object-defined error is raised Update It's not throwing errors because Worksheets("Fijos") exists and c.Offset(0, 2) is never out of range.

Worksheets is a collection of objects not a single object. Collections use {Key, Value} pairs to store Objects or values. Keys are unique. I you try to add the same key twice to a collection it will throw an error. If you try to retrieve a value for a collection, it will throw an error. Knowing that let's step throw the line of code.

If Not Worksheets("Fijos") Is Nothing Then
- If: If what? Let's compare stuff - Compare what? - Not - Not what? - Worksheets("Non Existing Key") - What is Worksheets("Non Existing Key") - It's a value stored in Worksheets -> Worksheet collection - What is the value of Worksheets("Non Existing Key")? - At this point the [in Worksheets -> Worksheet collection] throws the #9 Subscript out of range error. - Execution stops. - The second part of the comparison is never reached - Is Nothing is never evaluated

If you need to know if an item exists in a collection you have to trap the Subscript out of range error.

Function hasWorkBook(WorkbookName As String)
    On Error Resume Next
    Call Workbooks(WorkbookName).Name
    If Err.Number <> 0 Then
        hasWorkBook = False
    Else
        hasWorkBook = True
    End If
    On Error GoTo 0
End Function

Function hasWorkSheet(xlWorkbook As Workbook, SheetName As String)
    On Error Resume Next
    Call xlWorkbook.Worksheets(SheetName).Name
    If Err.Number <> 0 Then
        hasWorkSheet = False
    Else
        hasWorkSheet = True
    End If
    On Error GoTo 0
End Function

Why does it throw the error then? And why won't it bring the data from the other sheet? — KeOt777, Jun 15 '16 at 04:36

Object Required Error on Excel VBA when getting info from another sheet

2 Answers2