In & Out Array processing changes data

[xladept]

Hello All,

I've come across a perplexing situation wherein when I pull all the data into an array, operate on elements within that array and then replace the array in its original spot some of the fields (fields not operated upon) change themselves into date format. Have any of you ever encountered this phenomenon?

[JapanDave]

Yes, I have had that happen and I could not figure out why. In the end I worked out that b/c the data was from an external file it needed to be cleaned of any of the formatting.

[JBeaucaire]

Excel tries to be helpful. It would be informative if you provided some examples of the BEFORE value and the AFTER value. I'm going to guess the BEFORE value does have the semblance of date elements, enough that as the data is written into the cells at the end Excel recognizes it as a date format and converts it.

[xladept]

@Dave - thanks Dave, but what do you do with the format - I never see the external file, I get that from the client.

@Jerry - I'll give an example next time I have my 2010 up (it's only on my laptop) - but it doesn't look like date material, although there is a dash.

[xladept]

Hi All,

This program:

Please Login or Register  to view this content.

The original field data is 03-1972 The rendered field data Mar-72 showing from 3/1/1972 in formula bar.

BTW - both ww and w1 have in excess of 100k rows and the program took 49 mins to run - any suggestions?

[snb]

my suggestion: post a sample workbook

[JBeaucaire]

There you go... 03-1972 is absolutely a date string that Excel could and does "spot" converting it to March 1, 1972.

Suggestion:
1) make triple sure the column holding these values is formatted as TEXT before writing into the cells again
2) Skip this column with inserting your array of data
3) Copy the original values safely to another part of the sheet and back again after the array writes back.

[xladept]

@snb - I can't post the workbook as it belongs to the client.

@Jerry - I used option three - I considered option two - but I've had bad luck with option one in the past - thanks!

[snb]

@xladept

I asked for a sample workbook.

[xladept]

@snb -

The thing is that the problem is in its size, the one sheet is 113K X 3 and the other is 118K X 72.

Did you notice some inefficiency in my code? The records cannot be sorted (unless you propose some device to reorder them after processing).

[snb]

Size is never a problem in a workbook that only contains a representative sample of the data you are working with.

You make your code much more readable if you omit the : and put each line of code it's own codeline.
Using 'activate' or 'select' is inherently inefficient.
Looping to check whether a sheet exists falls in the same category.
Using several 'finds' to determine the builtin 'usedrange' isn't elegant either.

[xladept]

@snb - Thanks for the critique - I believe that the readability issue is a matter of taste - I like those "finds" to determine my parameters - I wanted the Master showing, which it wouldn't if I had to add the auxiliary sheet - how would you suggest that I find the auxiliary sheet?? - But, I was hoping for some suggestions that would make the code run faster

[snb]

Post a sample workbook and I can show you a much faster approach.

[xladept]

Hi snb,

I can't resist your offer:

https://www.dropbox.com/s/aa64d4k1i1...pleMaster.xlsx

Sorry about this but the uploader didn't work today

[snb]

Is sheet 2 representing the desired result ?

Can you describe the desired resutl ?

[xladept]

Hi snb,

The sample sheet1 is the master ww in my program, sheet2 (w1) is the data to be inserted onto sheet1 and sheet 3 (w2) is the repository for the master items that didn't match. Basically, the program adds the sheet 2 data (A-C) to the sheet 1 data (B-D) then at the bottom appends the sheet 2 data that found no match and, finally, the master records that didn't match are collected on sheet 3.

[snb]

To make a start:

Please Login or Register  to view this content.

[xladept]

Hi snb,

This qualifies for me as the caveat in your signature - it's a glaring hole in my development! Can you steer me in the direction that will allow me, in time, to understand this kind of code??

This code is really a lot faster than my code!!

And, thanks for this!

[snb]

I feared your 'next' question.

Especially for you I wrote this for an answer:

http://www.snb-vba.eu/VBA_Dictionary_en.html

[xladept]

Thanks snb - I've now got reading material for a good while to come!

The dictionary must be populated sequentially it seems, so I'll have to adjust my random mind

[snb]

Yes, your coding was clearly demonstrating it's origin ..

[xladept]

I'll start with "getting my nose out of joint"

The ability to write that blazing fast code must exacerbate hubris.

How would you write the rest of the routine??

[snb]

I already gave you a clue: if an item in sn exists in sp, sn(j,1) will be 'cleaned'; so all items that have not been found contain some value in sn(j,1)

[xladept]

It takes time for an "old dog" when there are new tricks - I'll be pondering

Duhhhhhhhhhhhhhhhhh

[xladept]

This is how I made it work:

Please Login or Register  to view this content.