Changing absolute address to a relative address

  • Thread starter Thread starter Highlystrung
  • Start date Start date
H

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. "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,
 
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. "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,
Click to expand...
 
Dave, thanks for the advice, I'll try it out in the morning,
--
thanks, Neil


Dave Peterson said:
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. "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,
Click to expand...
Click to expand...
 
Back
Top