Quotes in Excel Formula

[gerhardl]

Hi there,

How can I include double quotes WITHIN an excel formula I use in a macro?

XLSheet1.Range("B1").Formula = "=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)"

Here is some more detail:

Using a macro, I need to add this formula to Excel:

=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)

So my macro has: XLSheet1.Range("B1").Formula = "=INDIRECT("A" & INT(RAND()*COUNTA(A:A))+1)"

But because of the double quotes around "A", it gives me Expected end of statement

That A is supposed to refer to column A - if I use that exact formula in excel it works perfectly, but I cannot use it in the macro.

If I change it to A:A then the macro runs, but the formula does not work.

Any ideas?

[Anonymous]

Does it work if you exchange either the outer or inner double quotes with single quotes?

[gerhardl]

No, replacing the inner quotes gives me an Unknown runtime error

Replacing the outer quotes will change the formula to a comment.

[Anonymous · Accepted solution]

How about replacing one set of quotes with: chr(34)

This is the double quote ascii code.

I do recall there being some way to escape vbscript characters.. either using two double quotes or three..

like: "" instead of ", or possibly """

[gerhardl]

Ha - nice:

This worked:

XLSheet1.Range("B1").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

While I have your attention...

I am exporting account numbers to column A - they can be 19 or 20 characters long and they look like this:

10201128030000086054

But when I copy and paste them using my macro, they end up as 10201128030000000000.

Do you know how to fix that?

This doesn't work:

ActiveDocument.GetSheetObject("LB31").CopyTableToClipboard true

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

XLSheet1.Paste XLSheet1.Range("A1")

XLSheet1.Range("B1").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

Thank you.

[Anonymous]

Are you forcing the value to be loaded as text in QlikView?

In that case it sounds like Excel is transforming it to a numerical value and it doesn't handle above 15 digits if I remember correctly.

Try using the Text() function when loading the value into QV and see if it works.

[gerhardl]

No unfortunately that gives me the same result.