Vlookup

[STEVEB]

Hello,

I am trying to have a MACRO automatically do a VLOOKUP and paste the
results into my spreadsheet. I have input the formula manually and it
works, so I know the file names and ranges are correct.

Column A is the data to lookup and Column B is where I want the VLOOKUP
to enter the results.

My futile effort for a code is below:

Worksheets("Sheet 1").Activate

Set CurrentCell = Range("A2")
Do While Not IsEmpty(CurrentCell)
Set CurrentCell = CurrentCell.Offset(0, 1)
Set NextCell = CurrentCell.Offset(1, 0)

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1], '[FILE
NAME.xls]Sheet1'!$A$2:$B$1147,1,FALSE)"
CurrentCell.Copy
Selection.PasteSpecial Paste:=x1Values
Application.CutCopyMode = False
Set CurrentCell = NextCell

Loop

Next


End Sub


I Get a Run-time error 1004 - Application Defined or object defined
error.

Does anyone have any suggestions? I know this is probably an easy fix
but it is driving me crazy.

Thanks

 

[steve]

Steve,

You have mixed references in your formula

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1], '[FILE
NAME.xls]Sheet1'!$A$2:$B$1147,1,FALSE)"

Change to
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1], _
'[FILENAME.xls]Sheet1'!R1C1:R1147C2,1,FALSE)"

 

[STEVEB]

Steve,

Thanks for the help, The formula works great!

 

[steve]

Steve,

Glad to be of help!

Keep on Exceling...

 

[Tom Ogilvy]

Dim rng as Range
With Worksheets("Sheet 1")
set rng = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End With
rng.offset(0,1).Formula = _
"=VLOOKUP(A2," & _
"'[FILENAME.xls]Sheet1'!" & _
"$A$2:$B$1147,1,FALSE)"

rng.offset(0,1).Formula = rng.Offset(0,1).Value

Should do what you want.

One of your problems was/is you were mixing A1 notation and using
FormulaR1C1

You are using a 3rd argument of 1 in your vlookup - so are you not just
returning the a value that matches the value in Column A?