Does anyone know why this code

ActiveCell.FormulaR1C1 = "=HLOOKUP(A20,RC[1]:RC[100],1,FALSE)"

becomes this in the cell once inserted

=HLOOKUP('A20',RC[1]:RC[100],1,FALSE)

and the "added" apostrophes to cell ref 'A20' instead of A20 prevent the A20
text from being found by the HLOOKUP. If after inserted I manually remove
the apostrophes, the command works properly. How can I prevent VBA from
adding these apostrophes??

I would assume because you are using the FormulaR1C1 property, but using and
absolute address.
Maybe something like:

Dim RelRangeAdd As String

RelRangeAdd = Range("A20").Address(, , xlR1C1)
ActiveCell.FormulaR1C1 = "=HLOOKUP(" & RelRangeAdd &
",RC[1]:RC[100],1,FALSE)"

NickHK

"ble" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:96D0CA77-B93F-45F7-BFDF-(E-Mail Removed)...
> Does anyone know why this code
>
> ActiveCell.FormulaR1C1 = "=HLOOKUP(A20,RC[1]:RC[100],1,FALSE)"
>
> becomes this in the cell once inserted
>
> =HLOOKUP('A20',RC[1]:RC[100],1,FALSE)
>
> and the "added" apostrophes to cell ref 'A20' instead of A20 prevent the
> A20
> text from being found by the HLOOKUP. If after inserted I manually remove
> the apostrophes, the command works properly. How can I prevent VBA from
> adding these apostrophes??

ActiveCell.FormulaR1C1 = "=HLOOKUP(r20c1,RC[1]:RC[100],1,FALSE)"



ble wrote:
>
> Does anyone know why this code
>
> ActiveCell.FormulaR1C1 = "=HLOOKUP(A20,RC[1]:RC[100],1,FALSE)"
>
> becomes this in the cell once inserted
>
> =HLOOKUP('A20',RC[1]:RC[100],1,FALSE)
>
> and the "added" apostrophes to cell ref 'A20' instead of A20 prevent the A20
> text from being found by the HLOOKUP. If after inserted I manually remove
> the apostrophes, the command works properly. How can I prevent VBA from
> adding these apostrophes??

--

Dave Peterson

Thanks much, couldn't get to your response yesterday (problem with my
profile?), looked harder at other posts and discussions of R1C1, used
something like you suggested, once I understood R1C1.
--
ble


"NickHK" wrote:

> I would assume because you are using the FormulaR1C1 property, but using and
> absolute address.
> Maybe something like:
>
> Dim RelRangeAdd As String
>
> RelRangeAdd = Range("A20").Address(, , xlR1C1)
> ActiveCell.FormulaR1C1 = "=HLOOKUP(" & RelRangeAdd &
> ",RC[1]:RC[100],1,FALSE)"
>
> NickHK
>
> "ble" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:96D0CA77-B93F-45F7-BFDF-(E-Mail Removed)...
> > Does anyone know why this code
> >
> > ActiveCell.FormulaR1C1 = "=HLOOKUP(A20,RC[1]:RC[100],1,FALSE)"
> >
> > becomes this in the cell once inserted
> >
> > =HLOOKUP('A20',RC[1]:RC[100],1,FALSE)
> >
> > and the "added" apostrophes to cell ref 'A20' instead of A20 prevent the
> > A20
> > text from being found by the HLOOKUP. If after inserted I manually remove
> > the apostrophes, the command works properly. How can I prevent VBA from
> > adding these apostrophes??
>
>
>

Thanks, couldn't get to response yesterday, pretty much came to same
conclusion anyway after looking at other posts (should've done before asking).
--
ble


"Dave Peterson" wrote:

> ActiveCell.FormulaR1C1 = "=HLOOKUP(r20c1,RC[1]:RC[100],1,FALSE)"
>
>
>
> ble wrote:
> >
> > Does anyone know why this code
> >
> > ActiveCell.FormulaR1C1 = "=HLOOKUP(A20,RC[1]:RC[100],1,FALSE)"
> >
> > becomes this in the cell once inserted
> >
> > =HLOOKUP('A20',RC[1]:RC[100],1,FALSE)
> >
> > and the "added" apostrophes to cell ref 'A20' instead of A20 prevent the A20
> > text from being found by the HLOOKUP. If after inserted I manually remove
> > the apostrophes, the command works properly. How can I prevent VBA from
> > adding these apostrophes??
>
> --
>
> Dave Peterson
>