2

I first made a VBA script to compare two excel files. Then optimized it using Variant as said in this question. But then, I changed it to VBScript later. Here the method said above doesn't seem to work. Are there any other better ways to speed up the process? Especially for large files.

My core code is as follows:-

For Each cell In objxlWorksheet1.UsedRange
 If cell.Value <> objxlWorksheet2.Range(cell.Address).Value Then
  'fill the color in the cell if there is a mismatch and Increment the counter
  objxlWorksheet2.Range(cell.Address).Interior.ColorIndex = 3
  counter=counter+1
 End If
Next
Community
  • 1
  • 1
Rakesh Reddy
  • 136
  • 9
  • 1
    Read both ranges into arrays and do the comparison in memory. Your code touches the worksheet every time it has to read the value of the cells, that takes time. Create a third array that stores the colorindex -- store the number "3" for each matching value and "0" for non-matching. Then loop through that array and update the ColorIndex property blindly. – JimmyPena Oct 25 '13 at 20:22

1 Answers1

1

It depends on what it is that you are comparing. If you have two sheets with similar tables of data it would be easier to use formulas instead of VBA code. Just create a new worksheet and enter a formula like this: =Sheet1!A1=Sheet2!A1 Then you can use Ctrl-Find to search for False

Or if you can copy the data on one sheet side-by-side, you can use conditional formatting to highlight values that are different.

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • I have to delete some columns and also do some sort operations before I compare. This is supposed to be more of a generic code.(delete 1st and last column, not necessarily A, H always). So I used Vb Script. Also I am looking if there is some algo to do it better than the code I posted. It seems kind of rudimentary to me. – Rakesh Reddy Oct 25 '13 at 06:17
  • There is nothing wrong with your method, it just takes a long time. However if we knew more about what you're comparing and what you hope to accomplish maybe there could be alternate ways of doing the same thing that would not need a cell by cell comparison. – D_Bester Oct 25 '13 at 11:28