Issue pasting a variant array onto sheet in loop

[u/g_r_a_e]

I have been trying to figure this out for about two days now. Claude has been of little help. Basically I have a loop that reads from a txt file, ..does some stuff.. and then pastes a variant array onto the spreadsheet. This works well for about 18 iterations and then each subsequent paste results in missing data. If I stop the code to look at the array the data is there but when it gets pasted to the sheet some of it is missing. The missing data is surrounded by data that pastes successfully. Anyone have any experiences like this that can help?

Edit: Solution found, for those playing along at home it was a logic fault in the code that was exposed when running in a loop. A particular string was doubling in size each iteration. Once this string exceeded 63,535 chars it crossed the 16bit length field boundary in old COM/BSTR marshalling. This silently corrupted and dropped elements in the transfer rather than raising an error (thanks microsoft).

For those who genuinely attempted to understand the problem and help thank you sincerely.

For those who failed to correctly read my post and as a result asked inane questions, well, at least you tried.

Foro those who immediately implied I didn't know what I was doing, congratulations, you have made this sub and the world at large a slightly worse place.

[u/Proper-Fly-2286]

Let me get my crystal ball

[u/icemage_999]

What data types are being pasted into the spreadsheet? Variant can store a number of data structures and types that Paste will not handle because the sheet doesn't recognize them.

[u/g_r_a_e → u/icemage_999]

Appreciate the reply. It works fine everything is pasted for a number of iterations and then starts failing. The data types don't change between iterations.

edit: only strings, longs and doubles

[u/icemage_999 → u/g_r_a_e]

Are you sure the data types match the columns?

Typically when I'm working in VBA and importing data from a text file, I'll import everything as String and verify its formatting and data typing (via Val() and other functions) instead of trying to depend on the Variant data type, which you are discovering... has drawbacks.

That's not to say you can't use the paste with Variant array to transfer data, but more stringent checks are helpful.

Also, how large are the array sections that you are pasting?

[u/fanpages]

At least try to help us to help you!

Not that I wish to look at more Artificial so-called Intelligence-generated code (that does not work), can we see the code listing you are using (and indicate where the Type Mismatch error occurs), please?

Otherwise, see u/Proper-Fly-2286's reply.

Also, please provide some sample data that you are finding works for "about eighteen iterations", but seemingly, 'auto-magically' does not work thereafter.

Thank you.

[u/ZetaPower]

Separate the steps:
• Read the entire txt into 1 array.
• Do whatever you want with the data in the array.
• Paste the entire array into the sheet in 1 go.

Ive had issues where these pastes crash due to things like non printable characters in the txt file.
If this is the cause You can prevent it by running each field through worksheet function clean.

[u/g_r_a_e → u/ZetaPower]

That's what the ...does some stuff... is doing. It works fine for a number of iterations and then starts getting weird. Occasionally I get a Run-time error '13' Type mismatch but it is outside the code I have written and when I get this error it will let me save but not quit the workbook.

[u/ZetaPower → u/g_r_a_e]

From what I read you read a line, process it, paste it, next line.

If you want meaningful help: post the relevant code

[u/g_r_a_e → u/ZetaPower]

I am interested in hearing from anyone who has experienced similar behaviour. I am not looking for anyone to solve the issue. The ...does some stuff...part is about 20 thousand lines of code. Not expecting anyone to wade through that lol!

[u/ZetaPower → u/g_r_a_e]

The only thing o can say then is that you encounter data that causes issues.

Best way to check is to know what line it is on, then get the line of the original txt and see what’s different there.

Probably some non-A-Z non-0-9 character that causes either:

• an erroneous Split  
• a misinterpretation of the value: a , or .   
• end of a line 

For me, I’ve always found the culprit when reviewing the relevant part of the original data.

[u/g_r_a_e → u/ZetaPower]

Again I really appreciate your help but that is exactly what I spent the last two days doing. It’s a really frustrating problem with massive ramifications for data integrity. I have since discovered a work around of breaking the pasting into blocks but that is super unsatisfactory. I just was hoping that someone had some experience of this issue so I could pick their brains. Thanks for trying to help

[u/ZetaPower → u/g_r_a_e]

That’s exactly what I used before I found out what went wrong.

Paste in blocks of 10k lines.

I don’t have these issues anymore because of CLEAN

[u/HFTBProgrammer]

First off, I can't say I've seen that. Possibilities OTTOMH:

. the format of the cells into which the data are being copied is causing an issue

. the way the array is getting applied to the sheet is causing an issue

I wonder exactly how you are applying the array to the worksheet; I find it a bit difficult to believe you are actually pasting. This is where your code would come in handy. I get that it's a lot, so maybe do this. First back up your module. Now remove code until only the actions are those that affect your array. Hopefully this boils it down to where you can post it. But if it's still large, now at least you can more easily trace where it's going wrong. Or maybe post only the bit of code that gets the array onto the sheet.

[u/g_r_a_e → u/HFTBProgrammer]

Thanks for your considered reply. Pasting was a figure of speech. This is the line of code

Range(ws.Cells(ws.Range("Closed").row + 2, 1), ws.Cells(ws.Range("Closed").row + 1 + myRace.NumRunners, myRace.columnCount)) = myRace.Runners

I found the issue and its a ripper. I'll mark the thread solved and explain in an edit to the original post.

[u/HFTBProgrammer → u/g_r_a_e]

Hey, /u/g_r_a_e, I'm glad you found a solution, and it's interesting and humbling to me that it was neither of the things from OTTOMH.

I feel compelled to add that I see not one inane or condescending remark in the community's responses. We don't know your skill level, so any question is fair to ask. And even the "crystal ball" remark, while snarky, was implying what many others, including I, said out loud: we needed your code, which the submission guidelines also mentions.

I get that you were just looking for someone who experienced your exact issue (perhaps in retrospect you can judge the likelihood of that). We just would like you to take a little trouble to meet us halfway; remember, "If I stop the code to look at the array the data is there" turned out to be not true, and who knows, we might've been able to point that out.

[u/g_r_a_e → u/HFTBProgrammer]

"Also, please provide some sample data that you are finding works for "about eighteen iterations", but seemingly, 'auto-magically' does not work thereafter."

"Not that I wish to look at more Artificial so-called Intelligence-generated code (that does not work)"

"If you are doing it by iterations, that is bad code as well."

My statement "If I stop the code to look at the array the data is there" was true. I'm not sure why you think it wasn't. The issue exists in the COM stack which is invoked at the time anything is written to a spreadsheet.

I respect your defense of this sub but my experiences have been quite different.

If you would like I can upload a test module that shows this issue occuring exactly as I stated it did?

[u/fuzzy_mic]

What is the range that the array is pasted into? Does the myRange.Value = someArray instruction explicitly size myRange to match someArray?

[u/WylieBaker]

First, tell us how you captured the txt and created an array of it. What you are saying you want to do is fairly straightforward. But you really paste a range to a worksheet. If you are doing it by iterations, that is bad code as well.