Using vlookup in VBA

[jemwig]

I have the following VBA Code to extract a letter from a string in workbookA Sheet1

Sub jemwig()
Dim ce As Range
For Each ce In Range("A1:A100")
ce.Offset(0, 1).Value = Mid(ce.Value, 10, 1)
Next ce
End Sub

I need to use the extracted letter from the code above to be applied in a vlookup in a range (B3:C17 in workbookB sheet 1 and then return the lookup result from column 2 to a column of cells in WorkbookA sheet1

Would gratefully accept soultions thanks

 

[alansidman]

Calling Worksheet Functions In VBA

www.cpearson.com

 

[jemwig]

Calling Worksheet Functions In VBA

www.cpearson.com

Hi thanks for the pointer. Unfortunately I am still not being successful

What I really need to do is to:
1) extract the 10th character from a string in column A, and then
2) use that character to lookup a result in column 2 of a table in another workbook / worksheet range, and then
3) enter the lookup function result in column B. Then repeat for every row in the original column A

I hope that makes it clearer? TIA

 

[alansidman]

It would be very helpful if you uploaded some sample data that we can work with. Please use XL2BB to upload your sample. Do not post a picture as we cannot manipulate data in a picture.

 

[jemwig]

Hi I actually figured it out. Not very elegantly for sure but it works

For Each ce In Range("O2:O5000")

ce.Offset(0, 1).Value = Mid(ce.Value, 10, 1)

ce.Offset(0, 2).Value = Application.WorksheetFunction.VLookup(ce.Offset(0, 1), Workbooks("Personal.xlsb").Worksheets("Sheet1").Range("B3:C17"), 2, False)

Next ce

Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Range("Q1").Value = "Year"
Columns("Q:Q").EntireColumn.AutoFit