using VLookup with VBA

[pbobel]

Hi,

I am trying to use the vlookup function to populate a certain column with data from another spreadsheet. I am prompting the user to specify both the data files ( one which contains information and the one to populate). I keep getting an" Out of Range error" on the follwoing line:

Set rng = Workbooks("& RawFile").Sheets("Sheet1").Columns("C:K")

thanks.

 RawFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    ArchFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

  Set wkb1 = Workbooks.Open(ArchFile)
With wkb1
   lRow = Range("A" & Rows.Count).End(xlUp).Row

[b]
Set rng = Workbooks("& RawFile").Sheets("Sheet1").Columns("C:K")
[/b]
For i = 2 To lRow
    Cells(i, 23) = Application.VLookup((Cells(i, 3)), rng, 19, False)
    
Next i
End With

 

[PHV]

A starting point:

 RawFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set wkb0 = Workbooks.Open(RawFile)
Set rng = wkb0.Sheets("Sheet1").Columns("C:K")
ArchFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set wkb1 = Workbooks.Open(ArchFile)
With wkb1.ActiveSheet
   lRow = .Range("A" & Rows.Count).End(xlUp).Row
   For i = 2 To lRow
      .Cells(i, 23) = Application.VLookup((.Cells(i, 3)), rng, 19, False)
   Next i
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886