Replacing Code with words using vlookup
 

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(MyCe ll,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

Replacing Code with words using vlookup
 

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.

Replacing Code with words using vlookup
 

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.Offse t(0, -1),
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)


--
Hope that helps.

Vergel Adriano


"Arnold Klapheck" wrote:

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.

Replacing Code with words using vlookup
 

Thank you, that worked

"Vergel Adriano" wrote:

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.Offse t(0, -1),
Worksheets("Lookup").Range("$A$4:$B$10"), 2, False)


--
Hope that helps.

Vergel Adriano


"Arnold Klapheck" wrote:

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.