Excel VBA Error 'Excel Ran out resources' - Reg

Swaminathan Gopalan

New Member
Joined
May 4, 2012
Messages
6
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.

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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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

New Member
Joined
May 4, 2012
Messages
6
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?
 
Last edited:

Mohammad Basem

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
I meant something like this
Code:
[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

New Member
Joined
May 4, 2012
Messages
6
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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

New Member
Joined
May 4, 2012
Messages
6
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
Hi,

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

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

Swaminathan Gopalan

New Member
Joined
May 4, 2012
Messages
6
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

Well-known Member
Joined
Dec 24, 2011
Messages
1,218
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.
 

Forum statistics

Threads
1,082,344
Messages
5,364,812
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top