Incorrect format in formula

  • In the code below, the variables Steps1, Tested1, Steps2, Tested2, are 0 even though there is data. Thanks for your help.


  • Re: Incorrect format in formula

    Hi, and Welcome to OzGrid

    You seem to be trying to do two thinsg in one line. First you are setting the variable equa to the formul ain the cell, and then you trying (intending) to write the formula to the cell. Try this sequence:

    ws.FormulaR1C1 = "= COUNTA(" & qt & "A5:A5000" & qt & ")"
                Steps1 = ws.cells(1,1)
                ws.FormulaR1C1 = "=COUNTA(" & qt & "H5:J5000" & qt & ")"
                Tested1 = ws.cells(1,1)
                ws.FormulaR1C1 = "=COUNTA(" & qt & "L5:L5000" & qt & ")"
                Steps2 = ws.cells(1,1)
                ws.FormulaR1C1 = "=COUNTA(" & qt & "M5:N5000" & qt & ")"
                Tested2 = ws.cells(1,1)

    EDITED. Had thought initially you were not using ws as a range. And, I'm not sure I follow your code's syntax very well.

  • Re: Incorrect format in formula

    Thanks for your reply. I am new at this.

    I am using 'ws' to find a valid sheet; this works.
    Per valid sheet, I want to get the count of values in a column A and store the int in a variable using the excel function "=COUNTA(". I then want to do the same for column H. I then compair and if not 0, I then execute a function.

    I do not want to put a formula into a cell, if I am doing so then that is a mistake on my part.


  • Re: Incorrect format in formula

    Ok. I think you are looking for something like this.

    By the way, your code is looking specifically for cell A3 to be filled with (exactly) the five character string of the word Step preceded by a space. Is this what you intend?

  • Re: Incorrect format in formula

    Yes, that was it, it works great. Thanks you so much!

    Funny, the space before "step" isn't in my code. Strange.

    This forum is the best I have found, I have found 95% of my answers here. Thank you all!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!