Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question about excessive code in replacing #N/A when using VLOOKUP | Excel Discussion (Misc queries) | |||
Replacing VBA code strings by using VBA code? | Excel Programming | |||
Replacing hard code with reference to a cell value | Excel Programming | |||
Replacing code at runtime | Excel Programming | |||
Replacing code at runtime | Excel Programming |