| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing absolute address to a relative address
I'm trying to programme the creation of a vlookup table and created this
code with the macro recorder: ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet4!R1C1:R84C4,4,FALSE)" As the table I'm referencing (Sheet4) may expand I'm trying to pick up the table range in a variable using: strTableRange = Range.CurrentRegion.Select. However this gives me an absolute address e.g. "A1:D84". I need to convert this into an absolute address to go into the vlookup. Can you advise of best way to do this? many thanks, -- thanks, Neil |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing absolute address to a relative address
I'd let excel do the work:
Dim strTableRange As String strTableRange = Worksheets("sheet4").Range("A1") _ .CurrentRegion.Address(external:=True, _ ReferenceStyle:=xlR1C1) activecell.formular1c1 = "=vlookup(rc[-1]," & strtablerange & ",4,false)" Highlystrung wrote: I'm trying to programme the creation of a vlookup table and created this code with the macro recorder: ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet4!R1C1:R84C4,4,FALSE)" As the table I'm referencing (Sheet4) may expand I'm trying to pick up the table range in a variable using: strTableRange = Range.CurrentRegion.Select. However this gives me an absolute address e.g. "A1:D84". I need to convert this into an absolute address to go into the vlookup. Can you advise of best way to do this? many thanks, -- thanks, Neil -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing absolute address to a relative address
Dave, thanks for the advice, I'll try it out in the morning,
-- thanks, Neil "Dave Peterson" wrote: I'd let excel do the work: Dim strTableRange As String strTableRange = Worksheets("sheet4").Range("A1") _ .CurrentRegion.Address(external:=True, _ ReferenceStyle:=xlR1C1) activecell.formular1c1 = "=vlookup(rc[-1]," & strtablerange & ",4,false)" Highlystrung wrote: I'm trying to programme the creation of a vlookup table and created this code with the macro recorder: ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet4!R1C1:R84C4,4,FALSE)" As the table I'm referencing (Sheet4) may expand I'm trying to pick up the table range in a variable using: strTableRange = Range.CurrentRegion.Select. However this gives me an absolute address e.g. "A1:D84". I need to convert this into an absolute address to go into the vlookup. Can you advise of best way to do this? many thanks, -- thanks, Neil -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Formulas are showing relative address, rather than absolute | Excel Worksheet Functions | |||
| Convert Relative to absolute address | Excel Discussion (Misc queries) | |||
| How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
| what is difference between absolute address and relative address? | Excel Discussion (Misc queries) | |||
| changing relative to absolute | Excel Discussion (Misc queries) | |||
Linear Mode
