CODEWITHSUNDEEP
excelvba
Miriam List
Miriam List

Reputation: 3

Improper use of a Property

The code is supposed to compare two excel files and then create a new excel file showing the differences.

On the first sheet of the file, I’ve created a button and then opened it opened and put the code in. However, when I run the code I get an error saying

improper use of a Property

and .Worksheets is marked. Or the problem can be something related to Private Sub CommandButton1_Click()

I have done some research online and was unsuccessful so I’m hoping to get a bit of help.

Option Explicit

Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim report As Workbook 
Dim ws1 As Worksheet, ws2 As Worksheet
Dim difference As Long
Dim row As Long, col As Integer
Set report = Workbooks.Add

Set ws1 = ThisWorkbook.Worksheets(1)
Set ws2 = ThisWorkbook.Worksheets(1)

With ThisWorkbook.Worksheets("Sheet1")
    ws1row = .Rows.Count
    ws1col = .Columns.Count
End With
With ThisWorkbook.Worksheets("Sheet2")
    ws2row = .Rows.Count
    ws2col = .Columns.Count
End With

maxrow = ws1row
maxcol = ws1col

If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col

difference = 0

For col = 1 To maxcol
    For row = 1 To maxrow
    colval1 = ""
    colval2 = ""
    colval1 = ws1.Cells(row, col).Formula
    colval2 = ws2.Cells(row, col).Formula

    If colval1 <> colval2 Then
    difference = difference + 1
    Cells(row, col).Formula = colval1 & "<> " & colval2
    Cells(row, col).Interior.Color = 255
    Cells(row, col).Font.ColorIndex = 2
    Cells(row, col).Font.Bold = True
    End If
Next row
Next col

Columns("A:B").ColumnWidth = 25
report.Saved = True

If difference = 0 Then
report.Close False
End If
Set report = Nothing

MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets"

End Sub

Upvotes: 0

Views: 151

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

As per my comment, I would use a variable to refer to the worksheet of a specific workbook:

Option Explicit

Private Sub CommandButton1_Click()

 Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
 Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
 Dim report As Workbook, ws As Worksheet 'Declare ws to be a Worksheet variable
 Dim difference As Long, row As Long, col As Integer

 Set report = Workbooks.Add

 'Set the ws variable to be the first worksheet in the newly opened workbook
 Set ws = report.Worksheets(1)

 'You can then use the ws variable to refer to that specific worksheet
 'The variable is not affected by the sheet changing name or position
 'Example:
 ws.Name = "NewSheetName"
 MsgBox "The sheet at position " & ws.Index & " has changed names and is now called " & ws.Name

 'Rest of the code

Upvotes: 4

braX
braX

Reputation: 11755

Change this:

ActiveWorkbook.Worksheets ("Sheet1")

to this:

With ActiveWorkbook.Worksheets("Sheet1")

Same for Sheet2

Upvotes: 2

Related Questions