Help needed wtih Vlookup
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 |
Help needed wtih Vlookup
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 |
Help needed wtih Vlookup
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 |
All times are GMT +1. The time now is 04:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com