Steffmeister
Board Regular
- Joined
- Nov 7, 2005
- Messages
- 195
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
Tnx Steffmeister
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
Code:
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