Compare macro

[Steffmeister]

Hi guys,

I got another question about a macro and I cannot figure it out.
This macro compares a range of 2 workbooks. The workbooks will open by itself. The workbooks that I want to open are filled in the cell of the active sheet D6 and D7. When I push on the button, the 2 workbooks which are filled in D6 and D7 are open and compare ALL the addresses of column C (workbook1) and D (workbook2).
What I want is that when I fill in D8 a number (e.g. 1000) that he only compares the first 1000 addresses of the workbook what is filled in D6.
Is this possible? Below you see the code for so far.

Tnx for now

Sub CompareIt()
    Dim WF As WorksheetFunction
    Dim Sh As Worksheet
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim r As Long
    Dim Cell As Range
    Dim i As Long
    Set WF = WorksheetFunction
    Set Sh = ActiveSheet
    Set Sh1 = Workbooks.Open(Sh.Range("D7").Value).Worksheets(1)
    Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1)
    Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
    Sh.Columns(1).Cells.Clear
    r = 1
    For Each Cell In Rng
        On Error Resume Next
        i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
          If Err <> 0 Then
            Err.Clear
            Sh.Cells(r, 1).Value = Cell.Value
            r = r + 1
        End If
        On Error GoTo 0
    Next Cell
    Sh1.Parent.Close
    Sh2.Parent.Close
End Sub

Tnx Steffmeister

 

[Rijnsent]

Maximum number of lines

Hi Steff,

try replacing this:

Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1) 
Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)

By this:

Dim maximumaddress as Long
Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1) 
maximumaddress = Sh.Range("D8").Value
Set Rng = Sh2.Range("D1:D" & maximumaddress)

If you'd like it to be even more fancy, you could also change the start-address, hope you can work that out on your own with this example.

Greetz,

Koen

 

[Steffmeister]

Re: Maximum number of lines

Hi Steff,

try replacing this:

Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1) 
Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)

By this:

Dim maximumaddress as Long
Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1) 
maximumaddress = Sh.Range("D8").Value
Set Rng = Sh2.Range("D1:D" & maximumaddress)

If you'd like it to be even more fancy, you could also change the start-address, hope you can work that out on your own with this example.

Greetz,

Koen

Tnx Koen this works great .

Vr. groeten Stephan :wink: