Vlookup using Vba code across two tabs

[Dine]

Hi Friends

I am trying to implement the excel vlookup function into my vba command.
I have a sheet with 2 tabs :

Tab 1 = "Base sheet" has 3 columns
Tab 2 = "ottwafield" has 2 columns.

We have to do a vlookup from Tab 1 to Tab 2 to get the data points from Tab 2.

Tab 1

Captu.JPG


Tab 2


Care.JPG

[p45cal]

Not a 100% sure which way the data are going but test:

Sub blah()
With Sheets("Base Sheet").Range("AE2:AE3541")
  .FormulaR1C1 = "=VLOOKUP(RC[-30],ottwafield!R1C1:R36C2,2,FALSE)"
  .Value = .Value
  .SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
End Sub

[Dine]

Thanks Friend...its working fine ...major change was the data set was dynamic in nature.

the below code works for the above requirement...Appreciate your help on this...thank you .



Sub test()
Range("a2", Range("a" & Rows.Count).End(xlUp)).Columns("ae:af").Formula = _
"=iferror(vlookup($a2,ottwafield!$a:$b,column(a1),false),"""")"
EndSub

[p45cal]

I needn't have bothered. You already had the answer.
You cross posted at Chandoo.org, which you do regularly… (as well as MrExcel at times) without providing links.
All forums have the same or similar rules on cross posting.

Have a read of: http://www.excelguru.ca/content.php?184
And please supply links in future.

[Dine]

Sure Friend ...sorry for the confusion.
Going Forward will supply links ...Apologies again