Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Ashan Kaya   on Sep 20 In MS Office Category.

  
Question Answered By: Isra Demir   on Sep 20

"Subscript Out of Range" error occurs when the below Macro is run to
compare excel  sheets are on different workbooks. This error doesn't
occur when the sheets are in the same workbook. Can someone help me
fix this.. Thanks in advance.

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim i As Integer, j As Integer
Dim rc1 As Integer, rc2 As Integer, cc1 As Integer, cc2 As Integer
Dim maxR As Integer, maxC As Integer, cv1 As String, cv2 As String

With ws1.UsedRange
rc1 = .Rows.Count
cc1 = .Columns.Count
End With

With ws2.UsedRange
rc2 = .Rows.Count
cc2 = .Columns.Count
End With

maxR = rc1
maxC = cc1

If maxR < rc2 Then maxR = rc2
If maxC < cc2 Then maxC = cc2

For j = 1 To maxC
For i = 1 To maxR
cv1 = ws1.Cells(i, j).Value
cv2 = ws2.Cells(i, j).Value

If cv1 <> cv2 Then
ws1.Cells(i, j).Interior.ColorIndex = 3
ws2.Cells(i, j).Interior.ColorIndex = 3
End If
Next i
Next j

End Sub


Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
'CompareWorksheets Worksheets("Export Worksheet"), Worksheets
("Sheet1")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Export Worksheet"), _
Workbooks("ACD.xls").Worksheets("Export Worksheet") ' Error
Occurs here
End Sub

Share: 

 

This Question has 1 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on show a button in my excel sheet Or get search suggestion and latest updates.


Tagged: