CODEWITHSUNDEEP
excelvba
Mickey B
Mickey B

Reputation: 49

Excel VBA Find Function getting runtime Error 1004

Every time I try to run this code I get this error: 'Runtime error 1004: Application defined or Object defined error' It specifically is not liking the "Find" Function, with out it, it runs fine.

My Code looks like this:

Public Sub main()
    Dim maxVal As Long
    Dim origValue As Long
    Dim CaseNumber As Long
    Dim FoundCell As Range

maxVal = Range("A1").End(xlDown).Row
For origValue = 2 To maxVal
    CaseNumber = Sheets("Complications").Cells(origValue, 1).Value
    FoundCell = Sheets("Complications").Cells.Range(a1, a50000).Find(What:=CaseNumber)
    If FoundCell Is Nothing Then
        Sheets("Complications").Cells(origValue, 1).Value = Sheets("Cases").Cells(origValue, 1).Value
    Else
    End If

    Next
End Sub

Any help would be much appreciated!

Upvotes: 0

Views: 1503

Answers (2)

user3598756
user3598756

Reputation: 29421

being Bruce Wayne the answer to your question, the following may help you to avoid possible future problems:

Public Sub main()
    Dim maxVal As Long
    Dim origValue As Long
    Dim FoundCell As Range

    With Worksheets("Complications") '<--| reference this sheet once and for all and rest assure you're dealing with it if not otherwise explicitly referenced
        maxVal = .Range(.Rows.Count, 1).End(xlUp).Row '<--| find the "real" last non blank cell in column A, should any blank cell precede before it
        For origValue = 2 To maxVal
            Set FoundCell = .Range("A1", "A50000").Find(What:=.Cells(origValue, 1).Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| always specify those 4 Find() method parameters
            If FoundCell Is Nothing Then
               .Cells(origValue, 1).Value = Sheets("Cases").Cells(origValue, 1).Value
            Else
            End If
        Next
    End With
End Sub

the comment on Find() method is due to the fact that any usage of it (even from Excel UI) sets those parameters as default for subsequent use of it. So it's better to always specify what you actually need every time.

finally should there be no Else clause to deal with then the code could collapse to

Public Sub main2()
    Dim maxVal As Long
    Dim origValue As Long

    With Worksheets("Complications") '<--| reference this sheet once and for all and rest assure you're dealing with it if not otherwise explicitly referenced
        maxVal = .Range(.Rows.Count, 1).End(xlUp).Row '<--| find the "real" last non blank cell in column A, should any blank cell precede it
        For origValue = 2 To maxVal
            If .Range("A1", "A50000").Find(What:=.Cells(origValue, 1).Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) Is Nothing Then .Offset(origValue - 1).Value = Sheets("Cases").Cells(origValue, 1).Value '<--| always specify those 4 parameters of Find() method
        Next
    End With
End Sub

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

Set FoundCell = Sheets("Complications").Cells.Range("A1:A50000").Find(What:=CaseNumber)

You just typed the range incorrectly.

Upvotes: 4

Related Questions