Vba Code To Enter Formula

  • Hi,


    I am using follwoing piece of code to enter formula in a cell.


    Code
    Sub Do_It() 
        ActiveCell.Value = "=If(ISERROR(INDIRECT(B63&""!F:F"")),0,  COUNTIF(INDIRECT(B63&""!F:F""),""A""))" 
    End Sub


    I am copying the above formula in the next cell using for loop. In the next cell formula will be:
    =If(ISERROR(INDIRECT(B64&""!F:F"")),0, COUNTIF(INDIRECT(B64&""!F:F""),""A""))


    Cell B63 and B64 contains different Worksheet name.However, I want to use Cell(i,j).Address in place of B63 or B64. Will it be possible and how do I change the code ?


    Please help


    Regards


    Angshuman

  • Re: Vba Code To Enter Formula


    angshuman,


    probably it is faster entering the formula without loop ( if you enter only twice ).


    anyway try:

    Code
    Sub Do_It() 
        ActiveCell.Value = "=If(ISERROR(INDIRECT("& cells(i,j) &"!F:F"")),0,  COUNTIF(INDIRECT("& cells(i,j) &""!F:F""),""A""))" 
    End Sub


    filippo

  • Re: Vba Code To Enter Formula


    Hi Filippo,


    I tried with your code. But it did not work. May be I could not explain it properly. let me tell you that cell B63 which is referred by Cells (j, K) contains the Worksheet name on which the formula will be calculated.


    Code
    ActiveCell.Value = "=If(ISERROR(INDIRECT(" & Cells ( j,  k ) & "!F:F"")),0,COUNTIF(INDIRECT(" & Cells(j, k) & "!F:F""),""Brand""))"


    This formula will calculate the number of times the term "Brand" appears in worksheet "August" under the column F. The worksheet name is stored in cell B63 which we are trying to refer by Cell (j,k)


    Please advise


    Regards


    Angshuman

Participate now!

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