Countif formula for multiple cells and sheets

    Hi

    I am attempting to write a macro that is across an array of sheets. To start I have only picked up 3 sheets but have approx 90. Thought if I could get 1st 3 to work I can add the remainder. All sheets are set the same.

    CODE HERE[Sheets(Array("Channel - GI", "Channel - HI", "Channel - FP")).Select Sheets("Channel - GI").Activate Range("E57,E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88").Select Selection.ClearContentsSheets(Array("Channel - GI", "Channel - HI", "Channel - FP")).Select Sheets("Channel - GI").ActivateActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C5)))" Range("E8:E18,E22 E32,E36:E46,E50:E60,E64:E74,E78:E88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C6)))" Range("F8:F18,F22 F32,F36:F46,F50:F60,F64:F74,F78:F88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C7)))" Range("G8:G18,G22 G32,G36:G46,G50:G60,G64:G74,G78:G88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C8)))" Range("H8:H18,H22 H32,H36:H46,H50:H60,H64:H74,H78:H88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C9)))" Range("I8:I18,I22 I32,I36:I46,I50:I60,I64:I74,I78:I88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C10)))" Range("J8:J18,J22 J32,J36:J46,J50:J60,J64:J74,J78:J88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C11)))" Range("K8:K18,K22 K32,K36:K46,K50:K60,K64:K74,K78:K88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C12)))" Range("L8:L18,L22 L32,L36:L46,L50:L60,L64:L74,L78:L88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C13)))" Range("M8:M18,M22 M32,M36:M46,M50:M60,M64:M74,M78:M88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C14)))" Range("N8:N18,N22 N32,N36:N46,N50:N60,N64:N74,N78:N88").SelectActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C15)))" Range("O8:O18,O22 O32,O36:O46,O50:O60,O64:O74,O78:O88").SelectSheets(Array("Channel - GI", "Channel - HI", "Channel - FP")).Select Sheets("Channel - GI").Activate Range("E57,E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=FalseEnd Sub} END CODE

    Column "A" RC1 is a concatenate of 5 cells.
    R4C# is the period number
    Lookup2 is on different sheet which is manual input sheet is concatenate of all in the row including period.
    This need to be pasted as values after the calculations as it is then emailed out.

    Any assistance is always appreciated. Thanks in advance

    Lee

    Re: Countif formula for multiple cells and sheets


    L7B

    The posting of your code does not correctly use [noparse]

    Code

    [/noparse] tags (see the link in my signature for correct usage of tags) and the newline characters used have not translated to the forum which makes it impossible for me to easily add the tags for you.

    You will need to Edit Post on your post above to correct this.

    Re: Countif formula for multiple cells and sheets


    Hi Rob

    Hit the wrong button at the end. Below is the full macro I have put breaks betweent the lines of each formula I am asking it to do which I think is where my problem is. Hopefully you might be able to help me.

    Thanks
    Lee

    Re: Countif formula for multiple cells and sheets


    Hi Rob,

    I have worked a result that is working well. Would you mind looking and seeing if there is a better way that wont corrupt.

    Code

    Code ends

    Thanks ;)

Participate now!

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

Register Yourself Login