Vlookup with VBA

[sahaider]

Hello All,

I am trying to work with this vlookup in VBA but it's failing .

The range where the vlook up is looking for value of entries in column C is kept on a separate sheet called "Site ID to Cell ID" and in range A2 to B30287 . This worksheet is part of the same workbook.

the code is failing to return a value for the vlookup.

here is the code:

r=2
lastrow = Worksheets("NR_Best server by SS-RSRP").Cells.SpecialCells(xlCellTypeLastCell).Row

With Worksheets("NR_Best server by SS-RSRP")

For r1 = r To lastrow

.Cells(r1, 3) = Left(.Cells(r1, 2), Len(.Cells(r1, 2)) - 6)

.Cells(r1, 5) = Right(.Cells(r1, 2), 5)

.Cells(r1, 6) = .Cells(r1, 4) & "N" & .Cells(r1, 5)

.Cells(r1, 7) = .Cells(r1, 4) & .Cells(r1, 5)


.Range("D" & r1) = Application.WorksheetFunction.VLookup(Range("C" & r1), Worksheets("Site ID to Cell ID").Range("A2:B30287"), 2, False)

Next

End With

Thanks
Syed

 

[jasonb75]

Looks like there is a dot missing in front of Range at this point

VLookup(.Range("C" & r1)

 

[sahaider]

Looks like there is a dot missing in front of Range at this point

VLookup(.Range("C" & r1)

hello jasonb75,
Thanks. Fixed the dot,, but still code is not returning any values in column D.

Thnaks

 

[jasonb75]

Are you getting a runtime error or an empty cell?

Either way, best guess would be no match found.

 

[sahaider]

Are you getting a runtime error or an empty cell?

Either way, best guess would be no match found.

empty cell.

 

[jasonb75]

As I said, most likely no match found. The problem appears to be the data, not the code.
If the problem is in the code, then it is not something that we will be able to find without the necessary data.

 

[ER_Neha]

Hello All,

I am trying to work with this vlookup in VBA but it's failing .

The range where the vlook up is looking for value of entries in column C is kept on a separate sheet called "Site ID to Cell ID" and in range A2 to B30287 . This worksheet is part of the same workbook.

the code is failing to return a value for the vlookup.

here is the code:

r=2
lastrow = Worksheets("NR_Best server by SS-RSRP").Cells.SpecialCells(xlCellTypeLastCell).Row

With Worksheets("NR_Best server by SS-RSRP")

For r1 = r To lastrow

.Cells(r1, 3) = Left(.Cells(r1, 2), Len(.Cells(r1, 2)) - 6)

.Cells(r1, 5) = Right(.Cells(r1, 2), 5)

.Cells(r1, 6) = .Cells(r1, 4) & "N" & .Cells(r1, 5)

.Cells(r1, 7) = .Cells(r1, 4) & .Cells(r1, 5)


.Range("D" & r1) = Application.WorksheetFunction.VLookup(Range("C" & r1), Worksheets("Site ID to Cell ID").Range("A2:B30287"), 2, False)

Next

End With

Thanks
Syed

Hello I checked your code with my data, and it works fine. Jason is right in case of your data no match is found. and i would suggest you to check the code by going step by step using debugger and see in the respective sheets , if you are getting what you want according to the steps.

 

[sahaider]

ok Pasted a snap of two worksheets. as you can see the data that is being looked into is available in the other sheet and not getting returned in the first sheet.

 

[jasonb75]

Cell format can't be correctly identified from those snaps, but if one is text format and the other is number then they will not match even though they are visually the same.

See if this works

VLookup(CLng(.Range("C" & r1))

Based on the information so far, that shouldn't cause any runtime errors.