Hi,

I use VBA code to get data from several similar worksheets to create
one worksheet. At the end of the macro I use Vlookup to get
additional data from another (completely different) worksheet (e.g.
sheet1)

I loop through the final sheet using the folowing code:


DestSheet.Cells(LastRowT, 14).FormulaR1C1 = "=VLookup(RC[-13],sheet1!R1C1:R7C2,2,0)"


(The Macro searches in column 1 and returns the value found in
sheet1(column 2 and prints it in row 14 of the destination sheet)

Question.

1. Although the code works great, the particular column contains
formulas. What I really want is the found values as values. Can anyone
tell me how to achieve this ? (Without using a workaround solution
such as copy the column and paste it as values)


2. Perhaps using Vlookup is not the right method. Is there a different
(better) way to get the job done ?



Help is appreciated,


Farmer