Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 """
Does it work if you exchange either the outer or inner double quotes with single quotes?
No, replacing the inner quotes gives me an Unknown runtime error
Replacing the outer quotes will change the formula to a comment.
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 """
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.
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.
No unfortunately that gives me the same result.