Vlookup formula entered by VBA, returns from another worksheet

[Withnails]

Hi
I am looking to enter a vlookup formula into sheet3 of a workbook. the
formula should start in cell D2 and continue down as far as there are
populated cells in columnC. The lookup value is in column A, the table array
in a different work sheet, and the col_index_num is D2 dowards (eg 4), the
lookup value also being in column A in this other worksheet.
As you can imagine, this is a bit beyond my limited VBA skills.... can you
help?

 

[Patrick Molloy]

is the tablearray range named? I'll assume name it's tablearray here


with worksheets("sheet3")
WITH .Range( .Range("D2"), .Range("C2").End(xlDown).Offset(,1) )
.FormulaR1C1 = "=VLOOKUP(Sheet2!RC1,tablearray,3,False)
END WITH
end with

you aren't too clear . Ie is teh item to be found in column A of sheet2? If
its column C of sheet3 use
.FormulaR1C1 = "=VLOOKUP(RC3,tablearray,3,False)


hope this help anyway

 

[Withnails]

thank you - that certainly works and helps.

the thing that i cannot see is how i can vlookup from another workbook
(apologies this probably want clear).

i imagine that this section would need altering: FormulaR1C1 =
"=VLOOKUP(Sheet2!RC1,tablearray,3,False)

but i cant seem to get it rollin' ok.....? Any idea, and thank you for the
lsat post!

 

[Dave Peterson]

Record a macro when you create that formula manually.

If the sending workbook is closed, then close the sending workbook and pretend
to edit that range (hit F2 followed by the Enter key is enough).

If that doesn't help, then share that recorded macro.