Battle with Error 2029

[EDUCATED MONKEY]

Win xp professional office 2003 ie 8
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<o> </o>
Battle with Error 2029
<o> </o>
<o> </o>
<o> </o>
<o> </o>
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> </o>
<o> </o>
For M = 12 To N 'find location of synopsis<o></o>
<o> </o>
<o></o>
Mystring = Worksheets("INCOMMING DATA").Cells(M, 1).Value<o></o>
<o> </o>
<o></o>
NewString = NewString & Mystring<o></o>
<o> </o>
<o> </o>
Next M
<o> </o>
<o> </o>
The data in column A is tested like this
<o> </o>
<o> </o>
=MATCH("Author Book ",A:A,0)<o></o>
<o> </o>
<o> </o>
the synopsis relating to this book lies between A12 and value supplied by the match, this value is the decremented by 1
<o> </o>
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> </o>
I have tried dim NewString and NewString as Vairant
<o> </o>
In this particular case the are four string between A12 and A24 that need to be joined together,
<o> </o>
published: <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Month="6" Day="11" Year="1999">11 Jun 1999</st1:date> <o></o>
<o> </o>
<o> </o>
this string is cell A16<o></o>
<o> </o>
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> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
this string is cell A17<o></o>
<o> </o>
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></o>
<o> </o>
this string is cell A19<o></o>
<o> </o>
<o> </o>
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> </o>
this string is cell A21<o></o>
<o> </o>
God is officially dead, and life is run from <st1:State><st1lace>California</st1lace></st1:State>. But then I don't care about these things. I care about signs from the heart. <o></o>
<o> </o>
<o> </o>
this string is cell A24<o></o>
<o> </o>
Search for Author Book
<o> </o>
The normal result 99 % of the time would be
<o> </o>
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> </o>
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></o>
<o> </o>
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> </o>
God is officially dead, and life is run from <st1:State><st1lace>California</st1lace></st1:State>. But then I don't care about these things. I care about signs from the heart<o></o>

 

[shg]

What line does it fail on?

You could add an error handler and look at the contents of all the variables.

 

[AlphaFrog]

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?

[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?

 

[EDUCATED MONKEY]

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

 

[EDUCATED MONKEY]

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?

[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?

perfect problem solved thank you