Excel VBA Error 'Excel Ran out resources' - Reg

[Swaminathan Gopalan]

Hi,
I am having 2 excels each with 50000 records. I am comparing both the excels by updating formula (Sheet1 data - Sheet2 data) in the first cell & then 'Autofill' the same formula to other cells using VBA code.

Sample Code:
ThisWorkbook.Worksheets(3).Cells(1, 1).Value = "=(" & NameSheet1 & "!A1-" & NameSheet2 & "!A1)"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:C1"), Type:=xlFillDefault
Range("A1:C1").Select
Selection.AutoFill Destination:=Range("A1:C5"), Type:=xlFillDefault
Range("A1:C5").Select
Where NameSheet1 & NameSheet2 are sheet names

The problem I am facing is, while comparing the formulas are getting updated correctly till 10000 rows but while updating next row I am getting error 'Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.' and formulas are not getting updated for other rows.
I have 50000 rows & 200 columns. I can guess that this problem might be due some memory related & not because of records/columns in sheet.Can anyone help me how to resolve this issue? Or please suggest me some better way to compare two excel files with more records like 50000 rows * 200 columns.

Note: I am following this method of comparison as cell by cell comparison is a time consuming process

Hope I have posted this at right place & if I am not post direct me to right place as this is my first post.

[TMS]

Try something like:

Please Login or Register  to view this content.

You don't need to autofill. The formula will adjust across and down.

Regards, TMS

[Swaminathan Gopalan]

Hi TMS,
Thanks for your quick reply. I tried the method you suggested & again i am getting the same problem

Regards,
Swami

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[TMS]

I tried this and, although it worked, it took a while:

Please Login or Register  to view this content.

And, oddly, even though the values all seem to be there, it still says "calculate" at the bottom of the display.

I'm running out of ideas!

Regards, TMS

[Swaminathan Gopalan]

Hi,

Reposted with code tag.

I am having 2 excels each with 50000 records. I am comparing both the excels by updating formula (Sheet1 data - Sheet2 data) in the first cell & then 'Autofill' the same formula to other cells using VBA code.

Please Login or Register  to view this content.

The problem I am facing is, while comparing the formulas are getting updated correctly till 10000 rows but while updating next row I am getting error 'Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.' and formulas are not getting updated for other rows.
I have 50000 rows & 200 columns. I can guess that this problem might be due some memory related & not because of records/columns in sheet.Can anyone help me how to resolve this issue? Or please suggest me some better way to compare two excel files with more records like 50000 rows * 200 columns.

Note: I am following this method of comparison as cell by cell comparison is a time consuming process

Hope I have posted this at right place & if I am not post direct me to right place as this is my first post

[Swaminathan Gopalan]

Hi TMS,

I tried again the same & its not working for me still.I have given below the formula i am using for comparison.

Please Login or Register  to view this content.

The formula is quite lengthy & whether it could be the reason for error? What does the formula is, it will find the difference between two sheet values and check if difference is 'zero'. If the difference is 'zero' then it will write the Sheet1 value & Sheet2 value in the corresponding cell of third sheet and if the difference is 'not zero' then it will write the Sheet1 value,Sheet2 value & their difference in a single cell

Please suggest if the formula i am using could be the reason?

[TMS]

It is a very long formula and I doubt that will help. However, being realistic, if you have 50,000 rows by 200 columns, that is an awful lot of data to compare ... it's 10 million formulae.

Not sure why you need to display both sheet values if the difference is zero ... surely, just the one value would be sufficient.

The only way that I can see this working is if you loop through the columns, applying the formula and converting the result to values.


Regards, TMS

[Swaminathan Gopalan]

Hi TMS,

I will try to display the values if the difference is only not zero and leave the cell as blank if the difference is zero . Let's see how it works. Also, let me try to loop through columns as you suggested.

I wish atleast this should work.

[royUK]

What's happened to the code tags in the first post?

[Swaminathan Gopalan]

Hi RoyUK,


In the first post i missed out the code tag.Hence i reposted it again with code tag in the post 6

[Swaminathan Gopalan]

Hi TMS,

I tried the method as said in post#9 but it also didn't work either.It is giving the same issue after looping through around 100 columns.

We will be getting more such files month on month to compare & it is really more important to get this code work since doing it manually is not at all possible.

It will be really helpful if anyone could help me to resolve this issue or suggest any precise method to do comparison of two excel files with more records like 50000 rows * 200 columns.

[TMS]

You could try adding a Helper column on each sheet and creating a formula to concatenate all cells in the row. Copy this down. Then you just have to compare the Helper columns. It's not ideal but it would narrow down the search and you could then do a separate detailed comparison exercise on the rows highlighted.

It rather depends on how many mismatches you get as to how quick/successful this approach would be.

There are UDFs for concatenating all cells in a range. Some examples:

http://www.cpearson.com/excel/stringconcatenation.aspx

http://www.vbaexpress.com/kb/getarticle.php?kb_id=580

Regards, TMS

[Swaminathan Gopalan]

Hi TMS,

You mean concatenate all the 200 columns data to one column? I hope this is not ideal for large amount of data.

Please let me know if i got it wrong.

[TMS]

Depends how much data there is. These are just suggestions for you to try.

Why do you need to compare two sheets with 50,000 x 200 cells? *That* seems excessive.

Regards, TMS

[Swaminathan Gopalan]

Because i have data from two different environments(say Dev & PROD) and i need to compare both

[TMS]

Ah, so a Configuration Database?

If that's the case, I'm not sure a cell by cell comparison will work. If there is a server or peripheral or application in one environment and not in the other, and if that means an extra row, it would throw the rest of the comparison off.

I somehow don't think this is a goer.

Regards, TMS

[Swaminathan Gopalan]

Hi TMS,

We have two files(from different environment) which will be the result of ETL jobs. And, the data in both the files have to compared.

[Swaminathan Gopalan]

Hi,

I have tried all possible ways but nothing worked out for comparing files of records around 50000 or more. So now i have started using 'Beyond Compare' to compare the files which is giving result in 10-15 minutes.

Thanks for all your suggestions which helped to learn new things.

However, i am asking this on curiosity,if anyone get some clue on how to compare two large excel files (like 50000 * 200 records),please post here so that it will be helpful in the near future for everyone.


Regards,
Swami