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.

[LEFT][B][U]Sample Code:[/U][/B]
ThisWorkbook.Worksheets(3).Cells(1, 1).Value = "=IF(Sheet1!H2-Sheet2!H2=0,CONCATENATE("Sheet1 Value: ",Sheet1!H2,CHAR(10),CHAR(10),"Sheet2 Value: ",Sheet2!H2),CONCATENATE("Sheet1 Value: ",Sheet1!H2,CHAR(10),CHAR(10),"Sheet2 Value: ",Sheet2!H2,CHAR(10),CHAR(10),"DIFFERENCE: ",Sheet1!H2-Sheet2!H2))"[/LEFT]
 
[LEFT]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[/LEFT]

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​

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​

​

 

[Mohammad Basem]

Hi Swaminathan Gopalan and welcome to the forum!

Just curious, since you are comparing via VBA why do you need the formula?

You can just enter the comparison results (as per the formula format) in sheet 3 reducing the volume of calculations Excel has to do.

 

[Swaminathan Gopalan]

Hi Basem,

Thanks for your reply. For writing the results we have to loop cell by cell & it is time consuming for a file with more records.Hence i am following this method of updating formulas in a cell & 'autofill' the same to other cells which will update the results.

And, i didn't get right what you are saying by 'You can just enter the comparison results (as per the formula format)' in your post. Can you please put it in detail?

 

[Mohammad Basem]

I meant something like this

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]With ThisWorkbook.Worksheets(3).Range("A1:C5")
    .Formula = "=IF(Sheet1!H2-Sheet2!H2=0, " & _
        "CONCATENATE(""Sheet1 Value: "",Sheet1!H2,CHAR(10),CHAR(10),""Sheet2 Value: "",Sheet2!H2), " & _
        "CONCATENATE(""Sheet1 Value: "",Sheet1!H2,CHAR(10),CHAR(10),""Sheet2 Value: "",Sheet2!H2, " & _
            "CHAR(10),CHAR(10),""DIFFERENCE: "",Sheet1!H2-Sheet2!H2))"
    .Value = .Value
End With[/COLOR][/SIZE][/FONT]

 

[Swaminathan Gopalan]

Hi Basem,

Thanks for your reply. I tried the solution you provided but i am still facing the same issue.

And, i have a question in your code in earlier post that is, it will write the values alone after updating the formulas in the cells right.

I am getting error while updating the formulas & if i use your code also it is throwing error while updating formulas.

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.

Thanks in advance.

 

[Mohammad Basem]

And, i have a question in your code in earlier post that is, it will write the values alone after updating the formulas in the cells right.

Yes, you are right. That was the point I have mentioned earlier; replacing the formula with the values.

I am getting error while updating the formulas & if i use your code also it is throwing error while updating formulas.

If the error is on the sheet, then this is more related to the data itself. Try to check the cells that are producing the error. But, if the error is in the code, then what is the error?

What are your Windows and Excel versions?

Is it possible it is a Windows rather than Excel problem?

Are there formulas in the source files? Is it possible to have a sample file?

 

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

Regards,
Swami​

 

[Mohammad Basem]

Hi,

Glad to know the problem has been solved, al-hamdullah.

Does "Beyond Compare" show the results of comparison in Excel format?

 

[Swaminathan Gopalan]

We can export the results in HTML format & then we have to open it in excel to save. Also, in beyond compare we can save differences alone.

 

[Mohammad Basem]

Interesting!

May be if you consider the differences only, you can have a macro saves the differences to a new sheet of separate file (probably text file).

Thank you very much for the feedback and good luck.