Changing absolute address to a relative address

[Highlystrung]

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. "A184". 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,

 

[Dave Peterson]

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)"

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. "A184". 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,

 

[Highlystrung]

Dave, thanks for the advice, I'll try it out in the morning,
--
thanks, Neil

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)"

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. "A184". 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,