PDA

View Full Version : [SOLVED] Vlookup using Vba code across two tabs



Dine
02-06-2016, 10:14 AM
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

15361


Tab 2


15362

p45cal
02-06-2016, 10:56 AM
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
02-06-2016, 11:22 AM
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),"""")"
End Sub

p45cal
02-06-2016, 11:42 AM
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
02-06-2016, 11:55 AM
Sure Friend ...sorry for the confusion.
Going Forward will supply links ...Apologies again