Added apostrophes from VBA to Excel??

[Guest]

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??

 

[NickHK]

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

 

[Dave Peterson]

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

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??

 

[Guest]

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

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

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??

 

[Guest]

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

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

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??