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

steveec · Nov 25, 2016

I am very new to VBA. I am trying to isolate a particular customers transactions on a spread sheet.
Column "A" contains the Customer payment method (prepaid or various other methods). Column "D" contains the customer we are shipping too, Column "L" contains the customer we are shipping from.

If the cell in column "A" has 'prepaid' I want to search column "D" for the customer name (jaba). I will then delete all rows which do not contain this customer. IF the cell in column "A" is not 'prepaid' I want it to search column "L" for the customer name, and delete all rows which do not contain this string. When I run the code provided below I get an 1004 error on the following script 'If cell3.Find(ContainWord) Is Nothing Then'. Any help would be much appreciated.

Due to intricacies on how logistics companies bill their clients the initial for loop is necessary. I may be shipping from Jaba to a third party however I am not interested in this bill as it is prepaid.

Sub DoNotContainClearCells()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range

Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range
Dim ContainWord As String

'What range do you want to search?
Set rng = Range("A:A")
Set rng2 = Range("D:D")
Set rng3 = Range("L:L")
'What phrase do you want to test for?
ContainWord = "Metso"

For Each cell In rng

If cell.Value = "prepaid" Then

'Loop through each cell in range and test cell contents
For Each cell3 In rng2.Cells
If cell3.Find(ContainWord) Is Nothing Then Rows(cell3).EntireRow.Delete
Next cell3

Else
'Loop through each cell in range and test cell contents
For Each cell2 In rng3.Cells
If cell2.Find(ContainWord) Is Nothing Then Rows(cell2).EntireRow.Delete
Next cell2

End If
Next
End Sub

yky · Nov 25, 2016

Rows(cell3).EntireRow.Delete: Try cell3.EntireRow.Delete.

derekryan · May 10, 2017

I have a similar problem with the following code.

Sub CheckBox6_Click ()
Worksheets("OfficeSheet2").Activate
DirectoryNo = 6
LevelNo = 2
strPath = Cells(DirectoryNo + FolderPointer, bSubDirLvl2)
FilName = Cells((RowOffset + DirectoryNo), SubFileName) <-----Fail here
'
Call mFileSelection
'
clearcheck
FolderPointer = 0
DirectoryBox = strPath
OfficeMenuLevel3
End Sub

ALL variables are defined (Public) in a Global Module.
If I set a breakpoint at the beginning of the sub and then single step to the failing line then 'Mouse Over' each of the three variables involved. ALL have the correct values. But Mouseing over The Cells statement shows the following:-
Cells((RowOffset + Directory...=<Application-defined or object-defined error>

But if I replace all the variable names with their actual value it works perfectly.

In my time, I have learnt several programming languages and VBA, is by far, the most frustrating I have encountered.

xenou · May 11, 2017

One possibility is that your code is in some sheet module (lets say you put your code in "Sheet1").

Now you are activating another sheet:

Code:

Worksheets("OfficeSheet2").Activate

But your code is still in Worksheet "Sheet1" and scoped to that sheet:

Code:

FilName = Cells((RowOffset + DirectoryNo), SubFileName)

what you intend is to references the other worksheet:

Code:

FilName = Worksheets("Sheet2").Cells((RowOffset + DirectoryNo), SubFileName)

In general, you should always qualify range references fully. I would only use unqualified Range() and Cell() references in a sheet module if I was working with cells and ranges in that sheet and that sheet only.

derekryan · May 13, 2017

Thanks for you reply. Sorry it took so long to get back but I'm waiting for a gall bladder operation (Painful).

I'm afraid that your suggestion doesn't work!! Although my code says Worksheets("OfficeSheet2") This is the only sheet used.
I'm changing between user forms but I have made sure that all the sub routines are in the correct locations.

I have also noticed, since this problem surfaced, if I add a new subroutine it will not access it unless I 'Call' it.

xenou · May 13, 2017

User forms? That also introduces yet another code context.
In general, fully qualify all your variables - especially if you are working from a user form. Don't use .Activate or .Select - never (with the one exception that the only purpose in doing so is for the convenience of a user so that they are "at the right place" to look at something - not so that you can access data in your code). Don't put code in a worksheet module or in a userform unless it directly relates to operations in that context.

I'm going on vacation today so you might have to post a new question (which you should have done in the first place - no reason to put your question in another thread when the only connection is the error number). Post all the relevant information and give details.

derekryan · May 13, 2017

Thanks for your help & advise.

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

steveec

New Member

Excel Facts

yky

Well-known Member

derekryan

New Member

xenou

MrExcel MVP

derekryan

New Member

xenou

MrExcel MVP

derekryan

New Member

Similar threads

Forum statistics

Share this page

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

steveec

New Member

Excel Facts

yky

Well-known Member

derekryan

New Member

xenou

MrExcel MVP

derekryan

New Member

xenou

MrExcel MVP

derekryan

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock