VBA VLookup Reference Problem
 

I'm trying to simplify the process of vlookups for others and have trie
the following. This is intended to look at the adjacent cell to th
left(containing a branch code) and fill in the corresponding branc
name. I have the Vlookup data on a hidden sheet in the workbook. M
problem is that the lookup range moves with the active cell. Any hel
would be greatly appreciated.
Thanks,
Scott


Selection.FormulaR1C1 = _
"=VLOOKUP(OFFSET(RC,0,-1),Lookups!R[-1]C[-1]:R[43]C,2,FALSE)"


Selection.Copy

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = Fals

--
Message posted from http://www.ExcelForum.com

VBA VLookup Reference Problem
 

Can't tell where your lookup range is from your sample because the
references are relative. Change them to absolute

instead of

Lookups!R[-1]C[-1]:R[43]C

something like

Lookups!R43C7:R200C8 Lookups!G43:H200

--
Regards,
Tom Ogilvy


"Scott1523 " wrote in message
...
I'm trying to simplify the process of vlookups for others and have tried
the following. This is intended to look at the adjacent cell to the
left(containing a branch code) and fill in the corresponding branch
name. I have the Vlookup data on a hidden sheet in the workbook. My
problem is that the lookup range moves with the active cell. Any help
would be greatly appreciated.
Thanks,
Scott


Selection.FormulaR1C1 = _
"=VLOOKUP(OFFSET(RC,0,-1),Lookups!R[-1]C[-1]:R[43]C,2,FALSE)"


Selection.Copy

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False


---
Message posted from http://www.ExcelForum.com/