dcsimg
Results 1 to 7 of 7

Thread: VBA - Execution error

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2019
    Posts
    1

    VBA - Execution error

    Morning,

    Please help me to optimize my code VBA (Excel) see below.
    Actually i have an execution error1004 at line " MyDataRealise.Cells(rg.Row, "A").EntireRow.Delete"
    Code:
    Dim clebudget As String
    Dim rg, rgmois As Range
    Dim reponse As Integer
    Dim MyDataRealise As Object
    
    clebudget = ListBoxBudgetReel.Column(1, ListBoxBudgetReel.ListIndex) & "/" & ListBoxBudgetReel.Column(0, ListBoxBudgetReel.ListIndex) & "-" & ListBoxBudgetReel.Column(3, ListBoxBudgetReel.ListIndex)
    
    Set MyDataRealise = Sheets("Realise").Range("F1").CurrentRegion
    Set rg = MyDataRealise.Range("F1:F10000").Find(clebudget, MyDataRealise.Range("F1"), LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not rg Is Nothing Then
       reponse = MsgBox("Voulez vous vraiment supprimer cet enregistrement ?", vbYesNo + vbQuestion, "Confirmation")
        If reponse = vbYes Then
            MyDataRealise.Cells(rg.Row, "A").EntireRow.Delete
            MsgBox "Enregistrement supprimé avec succès !", vbInformation, "Informations"
            ListBoxBudgetReel.RowSource = "Tabrealise"
        End If
    Else
        MsgBox "Impossible de supprimer l'enregistrement !", vbCritical, "Informations"
    
    End If
    Last edited by Shaggy Hiker; Apr 30th, 2019 at 03:58 PM. Reason: Added CODE tags.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,908

    Re: VBA - Execution error

    MyDataRealise.Cells(rg.Row, 1).EntireRow.Delete
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,495

    Re: VBA - Execution error

    Code:
    set rng = MyDataRealise.Cells(rg.Row, "A")
    rng.EntireRow.Delete"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,908

    Re: VBA - Execution error

    Quote Originally Posted by westconn1 View Post
    Code:
    set rng = MyDataRealise.Cells(rg.Row, "A")
    rng.EntireRow.Delete"
    Huh?
    Since when is "Cells" accepting alphanumeric Index for Row/Column?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,495

    Re: VBA - Execution error

    afaik they always have, only for column index though
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,908

    Re: VBA - Execution error

    You're right.
    Actually Row/Column are the Indexes for the Item-Property (with Item being the Default-Prop, so we can use directly with Cells).

    But since the OP wants to delete the entire Row why not use:

    MyDataRealise.Rows(rg.Row).Delete

    Something else: I'm not that familiar with "CurrentRegion"
    Is it possible that the OP tries to delete a row which is outside the specified Range?
    Because as i understand the Cell-Object, the Indexes are relative to the Object it is applied to
    (i.e. Sheets("Data").Cells(1,1) being cell "A1" to MyRange.Cells(2,1) which is the second cell in the first column of the specified Range, NOT SHEET!)
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,495

    Re: VBA - Execution error

    Because as i understand the Cell-Object, the Indexes are relative to the Object it is applied to
    i am sure that is correct, but confusing to work with

    MyDataRealise.Rows(rg.Row).Delete
    i think i might have tried that, but i am sure that or many other options would be able to work and i do not know why deleting the entirerow from a range would error, when making it into a range object then works without error

    i generally try to keep as close to the original code as possible, but will suggest an alternative if i think it would be much better, or if they are using active anything

    Is it possible that the OP tries to delete a row which is outside the specified Range?
    i do not believe that would matter, a index outside a range object still returns a valid range
    ?range("a1")(6).address
    $A$6
    ?range("b3:g9")(922).address
    $E$156
    again, i find this idea confusing to work with
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width