PDA

View Full Version : VBA help - Vlookup



PSL
04-25-2009, 05:12 AM
Hi,

Just started using VBA so am quite a newbie. :)

Am trying to include a vlookup as part of a macro. Here i'm looking to to set the lookup workbook and sheet. But am getting an error.

http://img17.imageshack.us/img17/7058/98745969.jpg


So in the pic above; one.xls is the original workbook and two.xls is the one i intend to lookup to.

Sub Vlookup()

a = Range("D1").Value

b = Range("D2").Value

c = Range("D3").Value



ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[a]b!R2C1:R7C2,c,0)"
ActiveCell.Select
End Sub


And in the code above, have set a as the desired workbook, b as the sheet and c as the column no. to lookup in the array.

On running the macro

http://img19.imageshack.us/img19/7959/46981094.jpg

Even after selecting the Workbook and sheet, am getting an error.
Looking for a code that would automatically set the workbook, sheet etc without prompting me to select the same.

Thanks,
PSL

mdmackillop
04-25-2009, 05:51 AM
You need to create a valid string

Sub Vlookup()

Dim a As String, b As String, c As String
a = Range("D1").Value
b = Range("D2").Value
c = Range("D3").Value

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & a & "]" & b & "!R2C1:R7C2," & c & ",0)"
End Sub

PSL
04-25-2009, 05:58 AM
You need to create a valid string

Sub Vlookup()

Dim a As String, b As String, c As String
a = Range("D1").Value
b = Range("D2").Value
c = Range("D3").Value

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & a & "]" & b & "!R2C1:R7C2," & c & ",0)"
End Sub



Thanks !

Got it.!

Regards