Trying to Diagnose a Memory Problem

skepticologist · Feb 2nd 2016

I've got one of those VBA dilemmas I'm having a hard time figuring out. In general, I've been getting "Out of Memory" messages, and submitted an IT ticket asking about an upgrade to the 4 GB the machine I'm using comes with. But now I'm getting the following run-time error: "Method 'Find' of object 'Range' failed." When I click on debug, the following line of code is highlighted:

Code

Range("A:A").Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Activate

The macro up to that point had been taking 20-30 seconds to run. When I put a stop on the line with the FIND method, the first part of the macro took less than a second to run . . . AND the FIND method went straight to "Summary" with no problem when I pressed the F8 key.

I've taken all the memory-saving steps I can find on Ozgrid, and I'm wondering if it's not really another kind of problem. I'm almost hoping it is because all the prospective users have only 4 GB of memory as well.

I will genuinely appreciate any information regarding similar problems, possible solutions and/or wild guesses.

Smallman · Feb 2nd 2016

Re: Trying to Diagnose a Memory Problem

Hi

How many rows are you using in Column A. Your code will run considerably faster if you make your range dynamic. Also it is very rare in VBA that you ever have to SELECT anything so your code is doing more than it needs to by activating the word Summary.

Take care

Smallman

**Batman** · Feb 3rd 2016

Re: Trying to Diagnose a Memory Problem

Your code is making an assumption that the worksheet you want to use is the active sheet in Excel. Consider whether it is possible that you are somehow running the macro when the wrong worksheet is active.

I suspect that, if you identify the worksheet to a worksheet variable in VBA, and avoid using the Select and Activate methods, your memory problem will go away.

Trying to Diagnose a Memory Problem

Participate now!

Share