Hi have found a macro on another forum that solved my problem: It copies cells from Sheet 2 matching Sheet 1.
If .Cells(i, 1) = WS.Cells(j, 1) And .Cells(i, 3) = WS.Cells(j, 3) And .Cells(i, 4) = WS.Cells(j, 4) Then
' If a match is found:
WS.Cells(j, 6) = .Cells(i, 1)
WS.Cells(j, 7) = .Cells(i, 2)
WS.Cells(j, 8) = .Cells(i, 3)
WS.Cells(j, 9) = .Cells(i, 4)
If I change the line to look for non matching records it enters them into Row K,L,M,N and then loops only through rows 2 and 3 and not down the spreadsheet creating the full list.
If .Cells(i, 1) <> WS.Cells(j, 1) And .Cells(i, 3) <> WS.Cells(j, 3) And .Cells(i, 4) <> WS.Cells(j, 4) Then
' If a match is found:
WS.Cells(j, 11) = .Cells(i, 1)
WS.Cells(j, 12) = .Cells(i, 2)
WS.Cells(j, 13) = .Cells(i, 3)
WS.Cells(j, 14) = .Cells(i, 4)
Can anyone advise me on why this is please? Here is the full code:
Sub test2()
Dim WS As Worksheet
Set WS = Sheets("Master")
Dim RowsMaster As Integer, Rows2 As Integer
RowsMaster = WS.Cells(1048576, 1).End(xlUp).Row
Rows2 = Worksheets(2).Cells(1048576, 1).End(xlUp).Row
' Get the number of used rows for each sheet
With Worksheets(2)
For i = 2 To Rows2
' Loop through Sheet 2
For j = 2 To RowsMaster
' Loop through the Master sheet
'below line checks for matches to variable number, file and field
If .Cells(i, 1) <> WS.Cells(j, 1) And .Cells(i, 3) <> WS.Cells(j, 3) And .Cells(i, 4) <> WS.Cells(j, 4) Then
' If a match is found:
WS.Cells(j, 11) = .Cells(i, 1)
WS.Cells(j, 12) = .Cells(i, 2)
WS.Cells(j, 13) = .Cells(i, 3)
WS.Cells(j, 14) = .Cells(i, 4)
' above copies in the data
Exit For
' No point in continuing the search for that company
ElseIf j = RowsMaster Then
' If we got to the end of the Master sheet
' and haven't found a company match
RowsMaster = RowsMaster + 1
' Increment the number of rows
For k = 1 To 4 ' Change 3 to however many fields Sheet2 has
WS.Cells(RowsMaster, k) = .Cells(i, k)
' Copy the data from Sheet2 in on the bottom row of Master
Next
End If
Next j
Next i
End With
End Sub
Aucun commentaire:
Enregistrer un commentaire