How can we do a vlookup in VBA?

[kunaaln]

I get the error
Method 'Range' of object '_Global' failed

Inwkbk = Macro-Flatten Data 2v2.xlsm
strVlookupsheet = BOQ Map
intRow = 2
intCol = 2

ThisWorkbook.Sheets(strInputSheet).Cells(intRow, intCol + 3) = Application.VLookup(ThisWorkbook.Sheets("2a").Cells(intRow, intCol), Range("'[Inwkbk]strVlookupsheet'!$R$6$C$3", "'[Inwkbk]strVlookupsheet'!$R$1000$C$4"), 2, False)

I am writing a vba to do a vlookup , taking the lookup value from the Vba workbook and range from another workbook . the point here is the workbook and sheet i m taking the value may change . So i have stired them in a string variable and used them in the range .

 

[somnath_it2006]

It should be something like this:

ThisWorkbook.Sheets(strInputSheet).Cells(intRow, intCol + 3).Value = WorksheetFunction.VLookup(ThisWorkbook.Sheets("2a").Cells(intRow, intCol).Value, Workbooks(Inwkbk).Sheets(strVlookupsheet).Range("[COLOR="Red"]$R$1000:$C$4[/COLOR]"), 2, 0)

There is some problem with your range.. Please check that..

 

[kunaaln]

<table style="border-collapse: collapse; width: 750px; height: 272px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 197pt;" width="262"> <col style="width: 362pt;" width="482"> <tbody><tr style="height: 27.75pt;" height="37"> <td class="xl68" style="height: 27.75pt; width: 197pt;" height="37" width="262">FL001</td> <td class="xl69" style="border-left: medium none; width: 362pt;" width="482">Botticino</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL002</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Travertino marble (Brown Colour Italian Marble)</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL003</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Jaisalmer </td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2">FL004</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FLBOT</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Green Marble</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2">FL006</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL007</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Greek Thasos</td> </tr> <tr style="height: 2.25pt;" height="3"> <td class="xl68" style="height: 2.25pt; border-top: medium none;" height="3">FL008</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL009</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">White Satuario</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2">FL010</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Perlatto Sicillia</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2">FL012</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL013</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Rosa Alicante</td> </tr> <tr style="height: 2.1pt;" height="2"> <td class="xl68" style="height: 2.1pt; border-top: medium none;" height="2">FL014</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">FL015</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Grey William

</td> </tr> </tbody></table>

 

[kunaaln]

I am getting true and false as the return value

I need the the name that is present in the second column. i.e botticino and so on

thanks for your help
Its really urgent.

 

[somnath_it2006]

Is that workbook open where you want to lookup the data (Macro-Flatten Data 2v2.xlsm)?

What is the exact data range? ( This - $R$1000$C$4 is invalid)

 

[kunaaln]

'[Macro-Flatten Data 2v2.xlsm]BOQ Map'!R6C3:R1000C4

This is the range i get when i do a vlookup

 

[somnath_it2006]

Now try this:

ThisWorkbook.Sheets(strInputSheet).Cells(intRow, intCol + 3).Value = WorksheetFunction.VLookup(ThisWorkbook.Sheets("2a").Cells(intRow, intCol).Value, Workbooks(Inwkbk).Sheets(strVlookupsheet).Range("C6:D1000"), 2, 0)

or

    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = Workbooks("Macro-Flatten Data 2v2.xlsm")
    Set ws = wb.Sheets("BOQ Map")
    
    ThisWorkbook.Sheets(strInputSheet).Cells(intRow, intCol + 3).Value = WorksheetFunction.VLookup(ThisWorkbook.Sheets("2a").Cells(intRow, intCol).Value, ws.Range("C6:D1000"), 2, 0)

 

[kunaaln]

Both work fine , I tried both but ,it still giving True or False

 

[kunaaln]

ThisWorkbook.Sheets(strInputSheet).Cells(intRow, intCol + 3).Value = WorksheetFunction.VLookup(ThisWorkbook.Sheets("2a").Cells(intRow, intCol).Value, Workbooks(Inwkbk).Sheets(strVlookupsheet).Range("C6:D1000"), 2, 0)

This one Only Returns true and false
How is it possible to return Botticino for the lookup_value FL001

Thank you
​