Run-time error '1004'

davekim1000 · Jun 28th 2004

Hello all,

I am getting a "Run-time error '1004'" (application-defined or object-defined error when trying to run the following script:

Code

Sub CheckEmails()
    Dim Sht1Rng As Range
    Dim Sht2Rng As Range
    Sht1Rng = Sheets(1).Range("A1", Sheets(1).Range("A1").End(x1Down))
    Sht2Rng = Sheets(2).Range("A1", Sheets(2).Range("A1").End(xlDown))
     
    For c = 1 To Sht1Rng.Rows.Count
        If Not Sheets(1).Cells(c, 1) = "" Then
            If StringInColumn(Sheets(1).Cells(c, 1), Sht2Rng) = True Then
                If StringInColumn(Sheets(1).Cells(c, 1), Sheets(3).Columns(1)) Then
                     ' Do Nothing
                Else
                    Sheets(3).Cells(Sheets(3).Range("A1").End(xlDown).Row + 1, 1) = Sheets(1).Cells(c, 1)
                End If
            Else
                If Not StringInColumn(Sheets(1).Cells(c, 1), Sheets(4).Columns(1)) Then
                    Sheets(4).Cells(Sheets(4).Range("A1").End(xlDown).Row + 1, 1) = Sheets(1).Cells(c, 1)
                End If
                If Not StringInColumn(Sheets(2).Cells(c, 1), Sheets(4).Columns(1)) Then
                    Sheets(4).Cells(Sheets(4).Range("A1").End(xlDown).Row + 1, 1) = Sheets(2).Cells(c, 1)
                End If
            End If
        End If
    Next
    For c = 1 To Sht2Rng.Rows.Count
        If Not Sheets(2).Cells(c, 1) = "" Then
            If StringInColumn(Sheets(2).Cells(c, 1), Sht1Rng) = True Then
                If StringInColumn(Sheets(2).Cells(c, 1), Sheets(3).Columns(1)) Then
                     ' Do Nothing
                Else
                    Sheets(3).Cells(Sheets(3).Range("A1").End(xlDown).Row + 1, 1) = Sheets(2).Cells(c, 1)
                End If
            Else
                If Not StringInColumn(Sheets(2).Cells(c, 1), Sheets(4).Columns(1)) Then
                    Sheets(4).Cells(Sheets(4).Range("A1").End(xlDown).Row + 1, 1) = Sheets(2).Cells(c, 1)
                End If
                If Not StringInColumn(Sheets(1).Cells(c, 1), Sheets(4).Columns(1)) Then
                    Sheets(4).Cells(Sheets(4).Range("A1").End(xlDown).Row + 1, 1) = Sheets(1).Cells(c, 1)
                End If
            End If
        End If
    Next
End Sub
 
Function StringInColumn(SearchString As String, lstRange As Range) As Boolean
    StringInColumn = False
    For k = 1 To lstRange.Rows.Count
        If Range(lstRange).Cells(k, 1).Value = SearchString Then
            StringInColumn = True
            Exit For
        End If
    Next k
End Function

Display More

Apparently, the problematic script is the: Sht1Rng = Sheets(1).Range("A1", Sheets(1).Range("A1").End(x1Down))
Can anyone help me understand what is wrong here?

Jong · Jun 28th 2004

yeah

Change the x1down to xldown

the "1" should be an "l" (L)

davekim1000 · Jun 28th 2004

thanks Jon.

I changed the 1 to an l but now i get a run-timer error '91' object variable or With block variable not set. Where the corrupt script still resides in the Sht1Rng line of code.

Run-time error '1004'

Participate now!

Share