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?