Run Time Error 1004

[amoverton2]

Hi all!

So out of the blue I'm starting to get a run-time error with this code (Run Time Error 1004: Select Method of Range class failed) that used to have no issues... Can someone tell me why, or how to fix or change the code???

Note: I did try doing the suggested ways of fixing it to no avail via other posts and other online posts

Private Sub cmdDELETE_Click()
Dim account As String
Dim r As Long


If cmbDELNAME.Text = "" Then
    MsgBox "Enter Account Number"
End If

account = Trim(cmbDELNAME.Text)

r = 2
    Worksheets("O_ProspectiveGain_Add").activate
    Do While Worksheets("O_ProspectiveGain_Add").Cells(r, 2) <> ""
    If Worksheets("O_ProspectiveGain_Add").Cells(r, 2).Value = account Then
        Worksheets("O_ProspectiveGain_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_Sponsor_Add").activate
r = 2
    Do While Worksheets("O_Sponsor_Add").Cells(r, 2) <> ""
    If Worksheets("O_Sponsor_Add").Cells(r, 2).Value = account Then
        Worksheets("O_Sponsor_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop
Worksheets("O_AdminInfoGain_Add").activate
r = 2
    Do While Worksheets("O_AdminInfoGain_Add").Cells(r, 2) <> ""
    If Worksheets("O_AdminInfoGain_Add").Cells(r, 2).Value = account Then
        Worksheets("O_AdminInfoGain_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_LastContact_Add").activate
r = 2
    Do While Worksheets("O_LastContact_Add").Cells(r, 2) <> ""
    If Worksheets("O_LastContact_Add").Cells(r, 2).Value = account Then
        Worksheets("O_LastContact_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_TravelInfo_Add").activate
r = 2
    Do While Worksheets("O_TravelInfo_Add").Cells(r, 2) <> ""
    If Worksheets("O_TravelInfo_Add").Cells(r, 2).Value = account Then
        Worksheets("O_TravelInfo_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_FamilyInfo_Add").activate
r = 2
    Do While Worksheets("O_FamilyInfo_Add").Cells(r, 2) <> ""
    If Worksheets("O_FamilyInfo_Add").Cells(r, 2).Value = account Then
        Worksheets("O_FamilyInfo_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_MiscNotes_Add").activate
r = 2
    Do While Worksheets("O_MiscNotes_Add").Cells(r, 2) <> ""
    If Worksheets("O_MiscNotes_Add").Cells(r, 2).Value = account Then
        Worksheets("O_MiscNotes_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

    MsgBox "Member Deleted"
    
    ThisWorkbook.Save
    MsgBox "Saved"
    
End Sub

Thank you!!!

 

[amoverton2]

Okay, no more runtime error, however nothing is actually deleted...

Basically, the original code was to find a name (that was entered into a combobox (with a named range)) and delete the entire row in which that name was on in those specific sheets.

I think I know what it is, my guess is that it is not finding the Account specified on one or more of the sheets.
Try
Replacing this

  rngToDelete.EntireRow.Delete

With this

        If Not rngToDelete Is Nothing Then
            rngToDelete.EntireRow.Delete
        End If

 

[Alex Blakenburg]

Do you know how to look in the immediate window ?

For trouble shooting purposes, replace the code in my previous post with this, which adds some debug.print lines.
The show us what is in the immediate window.

Can you also show us what the data looks like in column 2 (the account column), preferably also showing the Row & Column references)

        If Not rngToDelete Is Nothing Then
            Debug.Print "Delete from sheet: "; sht.Name; vbTab; "Range: "; rngToDelete.Address
            rngToDelete.EntireRow.Delete
        Else
            Debug.Print "Nothing Deleted: "; sht.Name; vbTab; "account value "; account
        End If

 

[amoverton2]

I'm a dumbass... So I used your code but forgot to change the sheet names and was deleting something somewhere else in the workbook...

I'm pretty new with VBA and I appreciate your help and everyone else who has helped me out on my project... there a couple of questions coming so I look forward to the knowledge you guys/gals will bestow on me...

 

[Zot]

Need to add If sheet name is correct in check list ?

 

[Alex Blakenburg]

@amoverton2 Thanks for letting us know. Glad we could help.