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

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

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