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

Hi
one way: add the following line after your code:
DestSheet.Cells(LastRowT, 14).value=DestSheet.Cells(LastRowT, 14).value

--
Regards
Frank Kabel
Frankfurt, Germany


farmer wrote:
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

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

farmer wrote:
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