Run-time error '1004': Application-defined or object-defined error VBA

Question

I have searched for a solution and tried many options, but nothing seems to work.

I am also very new to VBA and you may think my code needs some work. By all means, if it makes sense change it. If you can be specific and explain your changes that would be great.

I am having trouble with the following line:

count = Application.WorksheetFunction.CountIf(Range(Cells(4, c), Cells(14, c)), Workbooks("Testing").Worksheets("Barrel MASTER").Range(BarrelSheet.Cells(r, "A")))

I have all the variables defined as best as I know how. When in debug mode and hovering over the last range in the above line, I get the desired result. The above line was working without the row variable in BarrelSheet.Cells(r, "A"). It worked when it was Cells("A15").

This is the rest of the code:

Sub Item_Location()

Dim c As Integer 'Counter to cycle through columns in cage inventory
Dim r As Integer 'Counter to cycle through Rows on MASTER sheets
Dim Finalcolumn As Integer
Dim ItemNum As Integer
Dim count As Integer
Dim multi As Integer 'Is there a repeat code in the same cage
Dim multioverall As Integer 'Is there a repeat code within 2 different cages
Dim ItemCode As String

Dim BarrelMasterB As Workbook
Dim BarrelSheet As Worksheet
Dim CodeCage As Worksheet

Set BarrelMasterB = Workbooks("Testing")
Set BarrelSheet = Workbooks("Testing").Worksheets("Barrel MASTER")

'Application.ScreenUpdating = False

'MsgBox ("This may take some time. When completed you will be prompted")

For r = 15 To 18
Workbooks("Testing").Activate
'ItemCode = Workbooks("Testing").Worksheets("Barrel MASTER").Range(BarrelSheet.Cells(r, "A")).Value

Finalcolumn = Range("L1").Column

ItemNum = Workbooks("Testing").Sheets(1).Range("B1000").End(xlUp).Row

multioverall = 0

'------------ Start of Searching for 1 Item code and 1 Cage -------------

multi = 0           'Resets to 0 for different cage
Workbooks.Open ("C:\Users\me\Desktop\Camp Spider Web\Inventory\Cage Inventory\Cage 1.xlsm")           'Opens Cage file to search for specified Code
Set CodeCage = Workbooks("Cage 1").Worksheets("Cage Inventory Coded")

For c = 5 To Finalcolumn
    count = Application.WorksheetFunction.CountIf(Range(Cells(4, c), Cells(14, c)), Workbooks("Testing").Worksheets("Barrel MASTER").Range(BarrelSheet.Cells(r, "A")))          'Counts how many

    If count = 1 And multi = 1 Then         'If there was a double code in the same item row
        MsgBox ("Repeated Code. Check Item Codes on Cage Inventory")
        c = 12           'Ending the search
    ElseIf count = 1 Then
        Workbooks("Testing").Worksheets("Barrel MASTER").Range(Cells(r, "E")) = Workbooks("Cage 1").Worksheets("Cage Inventory Coded").Range("M4")          'returning cage #
        multi = multi + 1       'Keep track of how many codes have been found
        multioverall = multioverall + 1
        If multioverall = 2 Then
            MsgBox ("There as been a second Item code found in Cage 1")
            GoTo Reset
        End If
    ElseIf count > 1 Then       'if there is a double item code in the same column
        MsgBox ("Repeated Code. Check Item Codes on Cage 1 Inventory")
        c = 12          'Ending the search for next column
    End If
Next c

Workbooks("Cage 1").Close SaveChanges:=True          'Saves and closes above cage

'-------- Start of Searching for 1 Item code and 1 Cage ------------

Next r

'Application.ScreenUpdating = True

'MsgBox ("Now Complete")
End Sub

Some of the lines have a ' in front, just so that when I am testing it, it doesn't run everything. There may also be some variables that are defined that I am not using. This is because I have tried many things and I will be removing them after. Some may be used at a later time.

The code will be run while the Barrel Master workbook is active, but opens the cage 1 workbook making it active. The searching section (code in-between -----) will be copied 32 times over, as there will be 32 different workbooks of info, all in the same format that this will need to search through. Thanks in advance! Cheers

Tim Williams Tim Williams · Accepted Answer · 2014-12-17T19:46:10

Try this:

count = Application.WorksheetFunction.CountIf(Range(Cells(4, c), Cells(14, c)), _
                                              BarrelSheet.Cells(r, "A").Value)

though you should really qualify every use of Range() or Cells() with a specific worksheet, otherwise you're relying on specific worksheets being active at specific times, which can lead to lots of hard-to-debug problems.

Run-time error '1004': Application-defined or object-defined error VBA

1 Answers