macro error

[lisa duncan]

I have a macro that should compare two worksheets; then any differences should be put on a third worksheet. The worksheets are very large and the range is from colums A to H and the rows are from 1 to 62512 on the second sheet and 1 to 60118 on the first sheet

Below is the macro when I run it I get an error of 400

Any help would be greatly appreciated!

Sub compareDiff()
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, LR&, i&, j&
Set WS1 = Worksheets("Sheet1") 'Change name as required
Set WS2 = Worksheets("Sheet2")
Set WS3 = Worksheets("Sheet3")


LR = WS1.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
For j = 1 To 8

If WS2.Cells(i, j).Text <> WS1.Cells(i, j).Text Then
WS3.Cells(i, j).Text = WS2.Cells(i, j).Text & " <> " & WS1.Cells(i, j).Text
End If
Next j
Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[GlennUK]

On what row do you get the error? Do you go into Debug to examine the variables, and see at what stage of processing the macro is at?

 

[lisa duncan]

The error I am getting is Runtime error 1004
Application different or object define error. I am unsure on how to correct

 

[GlennUK]

When the error happens go in the Debug mode to find out more info. You could then give more feedback to everyone here.

 

[MickG]

You could try removing the ".text" and rely on the default "Value".
You appear to be able to do this:-

Cells(1, 1) = Cells(2, 1).Text

But not this:-

Cells(1, 1).Text = Cells(2, 1).Text

See Text Property in VB Help

 

[lisa duncan]

Thanks this works!

this macro compares the worksheet line by line. and adds the differences line by line. this is a issue since one worksheet has several more rows with different criteria.

I have not figured out how to create a macro to compare all entiries in the entirework between 2 workbooks and only add the difference between the two on a third worksheet. Any suggestions would be greatly appreciated.

 

[MickG]

Can you show a short before an after example:-
You seem to be checking every cell against the same cell in another sheet.
I'm not really sure what result you expect.
Mick

 

[lisa duncan]

I expect the macro to search each row and each celll in sheet1 then search sheet 2 for the exact same criteria in each row then the result will be only the difference of what was found in sheet3 This should apply if sheet2 has additional rows with criteria that sheet1 does not.

 

[MickG]

At the moment you code checks each cell ( 8 columns) in sheet (1) against the same cells in sheet (2), if there difference it combines the text and displays the result on sheet (3).
So Some cells on sheet(3) are blank and others contain combined text.
Whats the difference between what you have and what you want, is it just to Check the entire sheet.??
Or perhaps its to add rows that are on one sheet and not on the other ??

 

[lisa duncan]

My code is looking at each cell in each column and putting all of the criteria on sheet3 regardless if it matches (see example below) this is row1 and row 2 of sheet1 and sheet2

so I have to review all of the results to find the unmatched results

Sheet I
<TABLE style="WIDTH: 527pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=700><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=66 align=right>18947</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 134pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=179> ABRAMSON KATHLEEN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=78>MILEAGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=56 align=right>2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=42 align=right>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=68 align=right>5/23/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=145>CROARKIN MARY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=66>RN11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>18947 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">ABRAMSON KATHLEEN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">MILEAGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>8</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 align=right>5/23/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">WADE GREGORY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">RN11</TD></TR></TBODY></TABLE>
Sheet 2
<TABLE style="WIDTH: 553pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=735><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=74 align=right>18947</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 120pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=160> ABRAMSON KATHLEEN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=78>MILEAGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=56 align=right>2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=42 align=right>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=68 align=right>5/23/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 143pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=191>CROARKIN MARY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=66>RN11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>18947 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">ABRAMSON KATHLEEN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">MILEAGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>8</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>5/23/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">WADE GREGORY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">RN11</TD></TR></TBODY></TABLE>

sheet 3(result)
<TABLE style="WIDTH: 728pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=969><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 283pt; mso-width-source: userset; mso-width-alt: 13787" width=377><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 6363" width=174><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=98>18947 <> 18947</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 283pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=377></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 131pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=174></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>2450300 <> 2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>6 <> 6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 144pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" width=192 colSpan=3>5/23/2011 <> 5/23/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>18947 <> 18947</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">2450300 <> 2450300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">8 <> 8</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colSpan=3>5/23/2011 <> 5/23/2011</TD></TR></TBODY></TABLE>
the result I am looking for is to only have what does not match in the entire workbook that are compared to be the result of sheet 3