Error 1004: Pastespecial method of range class failed, macro works sometimes!

**sam0287** · 12-19-2009, 06:38 AM

Hello,

I have a macro that works by pasting formulas into cells and then pasting over them with the values produced by the formulas. It works fine for 500 iterations then crashes at this line:

Please Login or Register  to view this content.

The macro has worked once, and if I reduce the total number of iterations required from 65 on i and j then it has worked, so I know that this isn't a problem with one of the calculations resulting in an error so not being able to paste properly.

I ahve seen similar help topics and tried to do what has been advised (i.e. remove 'selection' where I can and explicitly define the range instead) but so far no joy...the macro has actually worked in its entirety once, which just makes it even more infuriating!

If anyone can think of anything that may be going wrong I would be really grateful for some help. Thankyou in advance.

Here is the code in its entirety:

Please Login or Register  to view this content.

**teylyn** · 12-19-2009, 06:45 AM

Hi Sam,

welcome to the forum. I don't do VBA, but my first thought was:

any merged cells involved?

**sam0287** · 12-19-2009, 07:01 AM

Hey mate thanks for the quick response.

Unfortunately not no, although now that you remind me I guess it might be significant that it pretty much always messes up at the same cell.

In the 'Probabilities' sheet, row = 513, column number 39. columns 1-38 contain values so the paste values worked but from 39-137 the formulas remain so looks like its getting stuck on this cell. As I said before though the calculation itself does not poduce an error so it should be able to paste the value in, but it doesn'e hence I'm here

**pike** · 12-19-2009, 07:31 AM

Hi Sam0287
This bit wont work

Please Login or Register  to view this content.

what are you trying to do?

**shg** · 12-19-2009, 03:21 PM

I think this duplicates your code without the Selects:

Please Login or Register  to view this content.

**sam0287** · 12-19-2009, 03:27 PM

Hey mate,

Ye sorry its a little confusing, 'Exp(-Generalised Cost)' is actually the name of one of the worksheets, so that bit is just selecting the sheet by its name...

Generalised cost is a value (lambda) divided by the frequency, which is generated in the previous sheet... Lambda is defined in a cell in the workbook (as 1).

The model is a multinomial logit model, so its using the exponentials to calculate probabilities from a specified function involving exp(-generalised cost) for different costs. I've split the process up into stages in an attempt to make it easier to follow, since some people that will need to use it don't know vba, so the outputs from each stage are being stored in the workbook so that they can be checked...

I get the maths, but this programming malarchy is really throwing me off!

**sam0287** · 12-19-2009, 03:28 PM

Oh thanks a lot shg, I'll give it a try and report back.

Are selects a no no for this stuff then?

**shg** · 12-19-2009, 07:06 PM

Selecting doesn't disturb the logic, it just makes code run slower, and is rarely necessary.

But you should always qualify your references as a matter of good practice.

**sam0287** · 12-19-2009, 09:12 PM

Ok, solved the problem!

Turns out the worksheet I was trying to paste into where the code was screwing up was corrupted! After the macro failed I tried to paste in the formulas and then paste over them with values in the same row and it simply wouldn't let me do it...

So I deleted the sheet and recreated it to be identical to how it was originally and the code worked fine!

How infuriating, but nonetheless problem solved. Thanks to everyone for suggestions etc.

P.s. thanks shg, without the selections the code literally runs in half the time!
Will definitely avoid selections in future when possible...

Error 1004: Pastespecial method of range class failed, macro works sometimes!

LinkBack

Thread Tools

Rate This Thread

Display

Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Re: Error 1004: Pastespecial method of range class failed, macro works sometimes!

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions