6 Replies Latest reply: Sep 18, 2012 6:13 AM by Gerhard Laubscher RSS

    Quotes in Excel Formula

    Gerhard Laubscher

      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?