Help required for translating formula to a VBA command

[MSchädler]

Hi
I'm using in my sheet the following formula in column R =if(Q2<>"",VLOOKUP(Q2,Sheet2!A:B,2,FALSE),"").
What it is doing is: if in column Q there is a "TEXT" and If the equivalent "TEXT" is found in Sheet2, column A, get value in column B.

It would be great to have the equivalent vba command to do this operation.
Thank you for any help.
M.

 

[Marcelo Branco]

If you want to insert the formula in, for example, R2:R10 something like this

Range("R2:R10").Formula = "=IF(Q2<>"""",VLOOKUP(Q2,Sheet2!A:B,2,FALSE),"""")"

M.

 

[MSchädler]

Hello again Marcelo
I believe that my question is not understood. I use the formula =if(Q2<>"",VLOOKUP(Q2,Sheet2!A:B,2,FALSE),"") in each cell in column R.
When the macro copies the last row it paste the formula as well.

In order to make my sheet more efficient i would like to replace this formula and use a VBA command instead and put it in my existing macro.
Any suggestion how that is done?

Here is the link to my drop box for a test sheet: https://www.dropbox.com/s/dodz8e3zrycae89/Macro_Test_P000-1.xlsm?dl=0

Marc

 

[Marcelo Branco]

Hello again Marcelo
I believe that my question is not understood. I use the formula =if(Q2<>"",VLOOKUP(Q2,Sheet2!A:B,2,FALSE),"") in each cell in column R.
When the macro copies the last row it paste the formula as well.

In order to make my sheet more efficient i would like to replace this formula and use a VBA command instead and put it in my existing macro.
Any suggestion how that is done?

Here is the link to my drop box for a test sheet: https://www.dropbox.com/s/dodz8e3zrycae89/Macro_Test_P000-1.xlsm?dl=0

Marc

Marc,

This formula is very efficient. No need of vba loop or Find method to do the job.
If you want to get rid of the formulas, after they do what they are supposed to do, and stay only with values, try this.

With Range("R2:R10")
    .Formula = "=IF(Q2<>"""",VLOOKUP(Q2,Sheet2!A:B,2,FALSE),"""")"
    .Value = .Value
End With

M.

 

[MSchädler]

Hello Marcelo
Thank you for your help. In this case I will leave the formula in the cells.

Thanks
Marc

 

[Marcelo Branco]

Marc

If you have huge data an alternative is to sort the data and use approximate match to speed processing.
Take a look at these articles
https://fastexcel.wordpress.com/201...ps-part-1-using-excels-functions-efficiently/

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

M.

 

[MSchädler]

Marcelo
I will read these interesting articles.
Thanks
Marc