Option Explicit
Sub Import()
Dim fname As String
Dim Crng As Range, Frng As Range, Irng As Range
Set Crng = ActiveSheet.Range("C7")
Set Crng = Range(Crng, Crng.End(xlDown))
Set Frng = ActiveSheet.Range("F7")
Set Frng = Range(Frng, Frng.End(xlDown))
Set Irng = ActiveSheet.Range("I7")
Set Irng = Range(Irng, Irng.End(xlDown))
fname = ActiveSheet.Name
Workbooks.Open FileName:=Range("M2")
Sheets(fname).Select
Dim Erng As Range, aCell As Range, bCell As Range
Set Erng = ActiveSheet.Range("E5")
Set Erng = Range(Erng, Erng.End(xlDown))
For Each aCell In Crng
Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Offset(, -1) = bCell.Offset(, -1)
aCell.Offset(, 1) = bCell.Offset(, -2)
End If
Next
For Each aCell In Frng
Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Offset(, -1) = bCell.Offset(, -1)
aCell.Offset(, 1) = bCell.Offset(, -2)
End If
Next
For Each aCell In Irng
Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Offset(, -1) = bCell.Offset(, -1)
aCell.Offset(, 1) = bCell.Offset(, -2)
End If
Next
ActiveWindow.Close
End Sub
Could anyone please help me find whats wrong and need be fixing?
Bookmarks