How to use Arrays instead of Ranges

[Sachy]

Hello, I have text data (P and C letters) in one column and they are separated with blanks cells. I have created a macro which select each range (separated with blanks) and count the number of each letters in those ranges. If they are equal then continue (while its correct) and if not equal, add "Error" next to it. The macro is working, however it takes about 3 min to go thru 1000 rows of data, which is pretty slow. As far as I now the arrays should be a lot more efficient so please if anybody can show me how to implement it in my code or maybe theres a another solution how to do it quicker.

Please Login or Register  to view this content.

[TMS]

The solution is not to select the ranges and/or cells. Although you have switched off screen updating, you are still working your way through the cells and selecting each one. You don't need to do that.

Please post a sample workbook rather than a picture. Nobody can/will work with a picture.


Regards, TMS

[Andy Pope]

Please Login or Register  to view this content.

[Sachy]

Wow, thats really fast. This is exactly what I was looking for. Thank you very much Andy, your code is so effective and also so short - brilliant ! Please can you also describe it a little bit, for better understanding of each step. Thanks in advance

[Andy Pope]

Please Login or Register  to view this content.

[Sachy]

Thanks again :D

[Sachy]

One more question about arrays: In above code from Andy Pope (which was excellent) are specified two string cells ("P", "C") then we count and compare, how many times they appears in the Range, limited with blank cells.

I was wandering, how to utilize it a little bit (for better understanding of declaring arrays). When those strings cells will be integers (numbers) how to count and compare each number in that limited Range. If the numbers in that range will be equal to each other then resume next and if not add "Error" next to it. In this case there could be more different numbers in that Range but each of them should have their "partner" (same number) if not then add "Error". Any ideas how to do it ?

[Andy Pope]

Just change the data type of the array

Please Login or Register  to view this content.

Test on data where P replaced with 1 and C replaced with 2

[Sachy]

Hi Andy and thanks for your reply. Its not exactly what I am looking for, because it will only work for numbers 1 and 2. What I want to achieve, is that it will work for any number, not only 1 and 2. See the example:

[Andy Pope]

for that your do not need arrays.

Please Login or Register  to view this content.

[Sachy]

Thanks for the code Andy, I know there are other options how to do that, but please can you use arrays (dynamic) for this case instead of ranges, because I don't know how to that.The theory is: For each cell(number) in range separated by blanks find the same value, if found then resume next and if not then add "Error".
FYI: the above code should do exactly the same but its not working properly, e.g. if you have 3 numbers all of them have their copy in range (so the range contains 6 numbers) and you divide it with 2, then you get error (because 2 <> 3) - however in this case every number have its "buddy" so it should be ok

[Andy Pope]

If you are changing the rules and your data then you need to post new example.
Also you need to explain how the code should now what are the valid values.