View Single Post
 
Old 10-28-2015, 10:44 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,366
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim rSrc As Long, rTgt As Long, StrTmp As String
Dim xlShtTgt As Worksheet, xlShtSrc As Worksheet, xlRng As Range
On Error Resume Next
Set xlShtTgt = Workbooks("Main.xlsx").Sheets(1)
Set xlShtSrc = Workbooks("Database2.xlsx").Sheets(1)
With xlShtTgt
  Set xlRng = .Range("A1:A" & .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row)
End With
With xlShtTgt
  For rTgt = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    StrTmp = .Cells(rTgt, 3).Value
    With xlShtSrc
      rSrc = .Range("A1:A" & .UsedRange.Cells.SpecialCells(11).Row).Find( _
      What:=StrTmp, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
      MatchCase:=True, SearchFormat:=False).Row
    End With
    .Cells(rTgt, 3).Value = xlShtSrc.Cells(rSrc, 2).Value
  Next
  With .Columns(3)
    .WrapText = False
    .AutoFit
  End With
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote