Added apostrophes from VBA to Excel??
 

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

Added apostrophes from VBA to Excel??
 

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

Added apostrophes from VBA to Excel??
 

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

Added apostrophes from VBA to Excel??
 

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

Added apostrophes from VBA to Excel??
 

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