Excel VBA - Comparison two sheets

[NeewBie]

Hi,

I have this script to compare:

'Option Explicit

'Sub FindDuplicate()


'Dim LastRow As Long
'Dim MyRg1 As Range, MyRg2 As Range
'Dim A As Range
'Dim F
' Application.ScreenUpdating = False
'LastRow = Range("a" & Rows.Count).End(xlUp).Row
'Range("B1:B" & LastRow).ClearContents
'Set MyRg1 = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
'With Sheets("Sheet2")
'Set MyRg2 = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp))
'End With
'With MyRg2
'For Each A In MyRg1
'If (A <> Empty) Then
'Set F = .Find(What:=A.Value, After:=.Cells(1, 1), LookIn:=xlValues, _
'LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
'MatchCase:=False)
'If (Not F Is Nothing) Then
'A.Offset(0, 1) = .Cells(F.Row, F.Column + 1)
'End If
'End If
'Next A
' End With
'Application.ScreenUpdating = True

'End Sub

What I cant it to do is to colorize (if its works) when it find dublicates or that the script write all dublicates in worksheet 3. I also want it so search in the whole range in sheet 1 and sheet 2. Someone that can helps me?

Thank you!

 

[patel45]

Sub FindDuplicate()
Dim LastRow As Long
Dim MyRg1 As Range, MyRg2 As Range
Dim A As Range
Dim F
' Application.ScreenUpdating = False
LastRow = Range("a" & Rows.Count).End(xlUp).Row
Range("B1:B" & LastRow).ClearContents
Set MyRg1 = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
With Sheets(2)
Set MyRg2 = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With
With MyRg2
  For Each A In MyRg1
    If (A <> Empty) Then
      Set F = .Find(What:=A.Value, After:=.Cells(1, 1), LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False)
      If (Not F Is Nothing) Then
        A.Font.Bold = True
      End If
    End If
  Next A
End With
'Application.ScreenUpdating = True
End Sub

 

[NeewBie]

Sub FindDuplicate()
Dim LastRow As Long
Dim MyRg1 As Range, MyRg2 As Range
Dim A As Range
Dim F
' Application.ScreenUpdating = False
LastRow = Range("a" & Rows.Count).End(xlUp).Row
Range("B1:B" & LastRow).ClearContents
Set MyRg1 = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
With Sheets(2)
Set MyRg2 = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With
With MyRg2
  For Each A In MyRg1
    If (A <> Empty) Then
      Set F = .Find(What:=A.Value, After:=.Cells(1, 1), LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False)
      If (Not F Is Nothing) Then
        A.Font.Bold = True
      End If
    End If
  Next A
End With
'Application.ScreenUpdating = True
End Sub

Thank you for your answer but I think you missunderstood me. I dont want it to delete any values in sheet 1 or sheet 2. I want it do colorize OR write all dublicates in sheet 3 alternative write all non dublicates in sheet 3.

Greatful for all help!

 

[patel45]

did you test it ? my macro doesn't delete any values in sheet 1 or sheet 2.
Where did you find any command for deleting ?

 

[NeewBie]

Ofcorse i test it my friend. I wrote values in column A and B in both sheet when I was debugging it the values in sheet A was deleted, only the values that dont was dublicate.

 

[patel45]

attach please a link to your file

 

[HaHoBe]

Hi, NeewBie,

you delivered the code with the following line of code

Range("B1:B" & LastRow).ClearContents

This will clear all Contents from Column B. So if you want to see how the code works you either use column C for your check or put an ' (or REM) to the beginning of the above mentioned line which will not execute as it then will be treated as a comment.

@patel45:
you know for sure that the active sheet in the workbook is not the second sheet in the workbook? I pretty much doubt that as you ask for a link. Please be more precise about how to reference sheets considering their index, their tab names, their codenames.

Ciao,
Holger

 

[patel45]

@patel45:
you know for sure that the active sheet in the workbook is not the second sheet in the workbook? I pretty much doubt that as you ask for a link. Please be more precise about how to reference sheets considering their index, their tab names, their codenames.

I know for "sure" only that the user wrote, you know very well that I love links, then instead of externalize doubts about my requests provide your solution.

 

[NeewBie]

Thank you. Schould I just delete that part of the script?

 

[HaHoBe]

Hi, patel45,

you simplified the code from

With Sheets("Sheet2")

to

With Sheets(2)

Had you used

With Worksheets(2)

you would have excluded any posssible error (I´m not saying there will be but might be due to the different collections of both items).

then instead of externalize doubts about my requests provide your solution

There was a concern regarding the code which I explained. And I mentioned there might be an error when using Sheets(2).

did you test it ? my macro doesn't delete any values in sheet 1 or sheet 2.
Where did you find any command for deleting ?

Both in the opening post as well as your answer:

Range("B1:B" & LastRow).ClearContents

Ciao,
Holger