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

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. "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,
> --
> thanks, Neil

--

Dave Peterson

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. "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,
> > --
> > thanks, Neil
>
> --
>
> Dave Peterson
>