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