Battle with Error 2029

E

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
Win xp professional office 2003 ie 8
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
<o:p> </o:p>
Battle with Error 2029
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
This is the code that generates the error and I can not see why? As it runs for hours then for no apparent reason I get the error 2029
<o:p> </o:p>
<o:p> </o:p>
For M = 12 To N 'find location of synopsis<o:p></o:p>
<o:p> </o:p>
<o:p></o:p>
Mystring = Worksheets("INCOMMING DATA").Cells(M, 1).Value<o:p></o:p>
<o:p> </o:p>
<o:p></o:p>
NewString = NewString & Mystring<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Next M
<o:p> </o:p>
<o:p> </o:p>
The data in column A is tested like this
<o:p> </o:p>
<o:p> </o:p>
=MATCH("Author Book ",A:A,0)<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
the synopsis relating to this book lies between A12 and value supplied by the match, this value is the decremented by 1
<o:p> </o:p>
in between the two point there can be an amount of string of unknown length, normally it loops through the section of column A joining the strings together getting ready to pass the completed string to be store
<o:p> </o:p>
I have tried dim NewString and NewString as Vairant
<o:p> </o:p>
In this particular case the are four string between A12 and A24 that need to be joined together,
<o:p> </o:p>
published: <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:date Month="6" Day="11" Year="1999">11 Jun 1999</st1:date> <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
this string is cell A16<o:p></o:p>
<o:p> </o:p>
The inveterate popularity of this genre lies in mythologising the Mediterranean idyll as a world where stressed-out urbanites can lose their cares in lazy days and red wine. Like other role-models of its type, "Kissac"--Christopher Hope's home of six years--is peopled by the larger-than-life: a pathological collector of clocks and bones, a woman who believes in her own divinity, an alcoholic donkey.
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
this string is cell A17<o:p></o:p>
<o:p> </o:p>
Where this book transcends the genre, though, is in the playful subversion of the myth. Kissac is filled with immigrants all living the good life. Yet an air of dissatisfaction pervades their idyll, most apparently of all in the story of the British painter who subsists by selling melancholy ex-pats her body and Horlicks-- rekindling those memories of home that all exiles try to erase.<o:p></o:p>
<o:p> </o:p>
this string is cell A19<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Hope is a sensitive and witty writer whose sentences betray an interest in the emotions above all else. His subtly expressed disdain for our age, with its scientific certainties and slow betrayal of the feelings, saturates these memorable stories of love and death, and the curious symbiosis between the two:
<o:p> </o:p>
this string is cell A21<o:p></o:p>
<o:p> </o:p>
God is officially dead, and life is run from <st1:State><st1:place>California</st1:place></st1:State>. But then I don't care about these things. I care about signs from the heart. <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
this string is cell A24<o:p></o:p>
<o:p> </o:p>
Search for Author Book
<o:p> </o:p>
The normal result 99 % of the time would be
<o:p> </o:p>
The inveterate popularity of this genre lies in mythologising the Mediterranean idyll as a world where stressed-out urbanites can lose their cares in lazy days and red wine. Like other role-models of its type, "Kissac"--Christopher Hope's home of six years--is peopled by the larger-than-life: a pathological collector of clocks and bones, a woman who believes in her own divinity, an alcoholic donkey.
<o:p> </o:p>
Where this book transcends the genre, though, is in the playful subversion of the myth. Kissac is filled with immigrants all living the good life. Yet an air of dissatisfaction pervades their idyll, most apparently of all in the story of the British painter who subsists by selling melancholy ex-pats her body and Horlicks-- rekindling those memories of home that all exiles try to erase.<o:p></o:p>
<o:p> </o:p>
Hope is a sensitive and witty writer whose sentences betray an interest in the emotions above all else. His subtly expressed disdain for our age, with its scientific certainties and slow betrayal of the feelings, saturates these memorable stories of love and death, and the curious symbiosis between the two:
<o:p> </o:p>
God is officially dead, and life is run from <st1:State><st1:place>California</st1:place></st1:State>. But then I don't care about these things. I care about signs from the heart<o:p></o:p>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
What line does it fail on?

You could add an error handler and look at the contents of all the variables.
 
Upvote 0
Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.


A couple of questions:

Is this code looped over and over? If yes, do you reset the variable NewString or does the text string length grow to infinity?
Code: 
[COLOR="Red"]NewString = vbNullString[/COLOR]
For M = 12 To N 'find location of synopsis
    Mystring = Worksheets("INCOMMING DATA").Cells(M, 1).Value
    NewString = NewString & Mystring
Next M

When you get the VBA error, does the MATCH formula on the sheet have an error?

Are you using dynamic Named ranges? If yes, have they produced an error?
 
Last edited:
Upvote 0
thank you i will try addin the code and i shall try to remenber to put the code in correctly
it runs once per item next time it fails i shall look at the match to check tha value

regards pete
 
Upvote 0
AlphaFrog said:
Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.


A couple of questions:

Is this code looped over and over? If yes, do you reset the variable NewString or does the text string length grow to infinity?
Code: 
[COLOR="Red"]NewString = vbNullString[/COLOR]
For M = 12 To N 'find location of synopsis
    Mystring = Worksheets("INCOMMING DATA").Cells(M, 1).Value
    NewString = NewString & Mystring
Next M

When you get the VBA error, does the MATCH formula on the sheet have an error?

Are you using dynamic Named ranges? If yes, have they produced an error?
Click to expand...
perfect problem solved thank you
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top