Why would a code sometimes run fine and sometimes get a error in the same workbook?

  • I have a simple code to delete pictures from a merge cell and I am curious and trying to understand why sometimes it works and some time it get a error as: Run-Time error '13' Type mismatch on the line For Each xPic1 In ActiveSheet.Pictures.


    It is not a chart and it is the same kinda of picture ( a snipping from the internet snipped with this code "Application.CommandBars.ExecuteMso "ScreenClipping") and sometimes it works fine in all sheets and sometimes it only works in 1 sheet and get a error in others.

    Why it would works sometimes and sometimes not? in this case should I change the code?

  • I have never used "ScreenClipping" but one thing I always do is give every picture I put into a sheet a name. I use "Picture 1", "Picture 2" and so on but you can use whatever you like as long as it makes sense to you.

    I find it easier to work with named objects. An ounce of prevention is worth a pound of cure.

    As for why it sometimes works and other times not, I can't tell you unfortunately.

  • hi, Logit.

    Thank you for answering; However, it is not a option, the range with merged cell is needed specified for paste the snipping image from the internet.

    I am trying to understand why it works sometimes in 1 sheet but not in another and what can I do for it to work in all the sheet all the time the button clear all is clicked.

  • Hello Jolivanes,


    Thank you for answering. I dont know how many snips the excel user will put in the range.
    Would it be possibly to it if I am not sure if it will be only one or 4 pictures? is possible to use another code to delete everything that will be inside the range?
    Or maybe add something to my code so it would work all the time?

  • hi Roy,

    Thank you for taking the time to answer me.

    it is 3 sheets and all 3 sheets have merged cells to put this pictures and normally it will have 1 or 2 pics in few of them. All of them have the button clear all to be easy to delete everything inside (it will only be the snip from the internet, the same kind of pictures) at once.

  • While figuring out a way to replace your merged cells, try this in the meantime.

  • Roy,


    I still get the error Run-Time error '13': Type mismatch

  • Hello Jolivanes,


    The code runs fine with no error. However it does not delete the picture.
    The code runs, but nothings happens.

  • The code worked for me on a mocked up worksheet.


    You could try this,. To use this I would normally have the images named Pic1,Pic2 etc because it will delete all shapes without some checking. Try it on a bakup workbook


    Code
    1. Dim shp As Excel.shape
    2. For Each shp In ActiveSheet.Shapes
    3. shp.Delete
    4. Next
  • The code in Post #9 just replaces this

    Code
    1. Set xRg1 = Range("B75:K136")

    So you should end up with something like this.

  • Roy,

    Thank you so much, but unfortunately, is not possible for me to named the pics since I am not sure how many pics it will be. (It will depend on the excel user, sometimes it can be a lot like 4 and sometimes only 1).


    The code worked for me on a mocked up worksheet.


    You could try this,. To use this I would normally have the images named Pic1,Pic2 etc because it will delete all shapes without some checking. Try it on a bakup workbook


    Code
    1. Dim shp As Excel.shape
    2. For Each shp In ActiveSheet.Shapes
    3. shp.Delete
    4. Next
  • Jolivanes,

    The code is working perfectly fine, and it deletes the pics inside the merge cells. However, I am curious, would it be possible to use something else on the place of the merge cells?

    Thank you.

    The code in Post #9 just replaces this

    Code
    1. Set xRg1 = Range("B75:K136")

    So you should end up with something like this.

  • You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.


    https://stackoverflow.com/ques…-error-in-the-same-workbo

    Yes I did, and I am very thankful for all the help.

    I apologize I will reread the rules.

    It may appear silly, but I did not notice it was the same website or linked in some way.

    I will read the rules once more to make sure it will not happen again.

    I am sorry and thank you for all your help.

  • Re: "would it be possible to use something else on the place of the merge cells?"

    Use "CenterAcrossSelection" instead.

    BTW, how are the pictures put onto the Sheet?

    If that is done with code, you can name them as they are put onto the sheet.

    I use a code to put the pictures on the sheet.


  • You've had a lot of help here, but it seems that you haven't grasped the Forum Rules. especially cross posting.


    https://stackoverflow.com/ques…-error-in-the-same-workbo


    I just reread the rules ( The forum Etiquette* ) , and I understand what do you mean (to be honest with you, first I thought it was only about the same question here in this forum, but now I understood).

    I apologize; it will not happen again.

    Thank you for your time.