excel vb countifs

  • hi,


    i am new and learing excel VB, i have worked out below based on macro recording, but when i activate the macro i see formula bar showing '=COUNTIFS(summ_bycode!H:H,"=wong",summ_bycode!J:J,">=1")', anyone can help to convert to a propoer VB coding?


    Thank you very much for you help in advance.


    ----------------------

    Code
    Sub countifs_DAE()
    
    
    
    
    Range("E3").Select
        ActiveCell.FormulaR1C1 = _
            "=COUNTIFS(summ_bycode!C[3],""=wong"",summ_bycode!C[5],"">=1"")"
        
    End Sub


    ---------------------------

  • Re: excel vb countifs


    Hi Chhiew,




    I think this may solve your Problem

  • Re: excel vb countifs


    hi chand123, thanks for your guidance.


    a bit confused on criteria range & criteria 1, if my criteria range is h2:f100, and criteria 1 is column f2:f100, what is the code?


    if i my data range is on sheet2, how to refer it to sheet?


    thank you so much.

  • Re: excel vb countifs


    Hi,


    To all involved in this thread, if you are quoting VBA code, please use code tags. I have edited the earlier posts to add them.


    As a pointer, in case it helps, the easiest way to find the R1C1 format of a formula for VBA purposes is to write the formula manually in Excel, in the normal A1 format, then go into the Visual Basic Editor, Immediate Pane, and type:

    Code
    ? Activecell.FormulaR1C1


    and hit return. This will give you the formula you have just typed in R1C1 format. You might need to edit it for use in VBA by adding the extra " characters to return " in the formula.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

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