Replacing Code with words using vlookup

[Guest]

Column F is full of code words, I want to change them to english, I have a
table (on worksheet named lookup)that has the code words in the first column
and the english in the second column I was thinking of using vlookup with
something like this:
Dim intLastRow As Integer
intLastRow = [F65535].End(xlUp).Row
Dim MyCell, MyRng As Range

Set MyRng = Range("F1:F" & intLastRow)

For Each MyCell In MyRng

MyCell.Application.WorksheetFunctions.Vlookup(MyCell,Lookup!A1:B10,2)

Next MyCell

is there a better way or what do I need to do with this code to make it work.

Thanks, Arnold

 

[Guest]

Update:
A guy at office said must insert column then could hide column with code
words.

So I updated it to:

Dim intLastRow, intcount As Integer
Dim MyCell, MyRng As Range

intLastRow = [F65535].End(xlUp).Row
intcount = 1

Columns("G:G").Insert Shift:=xlToRight

Set MyRng = Range("G1:G" & intLastRow)

For Each MyCell In MyRng

MyCell.Value = Application.WorksheetFunctions.VLookup("F" & intcount,
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)
intcount = intcount + 1
Next MyCell

can't run the MyCell.Value line says "Object doesn't support this property
or method" any help would be appreciated.

 

[Guest]

Arnold,

try replacing this line:

MyCell.Value = Application.WorksheetFunctions.VLookup("F" & intcount,
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)


with this:

MyCell.Value = Application.WorksheetFunction.VLookup(MyCell.Offset(0, -1),
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)

 

[Guest]

Thank you, that worked

Arnold,

try replacing this line:

MyCell.Value = Application.WorksheetFunctions.VLookup("F" & intcount,
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)


with this:

MyCell.Value = Application.WorksheetFunction.VLookup(MyCell.Offset(0, -1),
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)


--
Hope that helps.

Vergel Adriano

Update:
A guy at office said must insert column then could hide column with code
words.

So I updated it to:

Dim intLastRow, intcount As Integer
Dim MyCell, MyRng As Range

intLastRow = [F65535].End(xlUp).Row
intcount = 1

Columns("G:G").Insert Shift:=xlToRight

Set MyRng = Range("G1:G" & intLastRow)

For Each MyCell In MyRng

MyCell.Value = Application.WorksheetFunctions.VLookup("F" & intcount,
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)
intcount = intcount + 1
Next MyCell

can't run the MyCell.Value line says "Object doesn't support this property
or method" any help would be appreciated.