Try...


Sub LookingUp_A()
Dim rngTarget As Range, rngLookup As Range, sFormula$

With ActiveWorkbook.Worksheets("MatchingNo").UsedRange
Set rngTarget = .Offset(1, 1).Resize(.Rows.Count - 1, _
.Columns.Count - 1)
End With

With ActiveWorkbook.Worksheets("SM35").UsedRange
'Lookup range starts at $A5 and stops at the last row/col
Set rngLookup = .Offset(4, 1).Resize(.Rows.Count - 4, _
.Columns.Count - 1)
End With
'Formula needs to self-adjust to its row in "A",
'so make its ref col-absolute/row-relative.
sFormula = "=VLOOKUP($A2,SM35!" & rngLookup.Address _
& ",COLUMN()+1,False)"

Application.ScreenUpdating = False
On Error GoTo ErrExit
With rngTarget
.Formula = sFormula: .Columns.AutoFit
.HorizontalAlignment = xlCenter: .VerticalAlignment = xlCenter
End With

ErrExit:
Set rngTarget = Nothing: Set rngLookup = Nothing
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion