Swaminathan
05-08-2012, 10:36 PM
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 = "=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))"
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
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
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 = "=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))"
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
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