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
Countif formula for multiple cells and sheets
-
-
-
Re: Countif formula for multiple cells and sheets
L7B
The posting of your code does not correctly use [noparse][/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
Code
Display MoreSub Input_Formulas() 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.ClearContents Sheets(Array("Channel - GI", "Channel - HI", "Channel - FP")).Select Sheets("Channel - GI").Activate ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C5)))" Range("E8:E18,E22 E32,E36:E46,E50:E60,E64:E74,E78:E88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C6)))" Range("F8:F18,F22 F32,F36:F46,F50:F60,F64:F74,F78:F88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C7)))" Range("G8:G18,G22 G32,G36:G46,G50:G60,G64:G74,G78:G88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C8)))" Range("H8:H18,H22 H32,H36:H46,H50:H60,H64:H74,H78:H88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C9)))" Range("I8:I18,I22 I32,I36:I46,I50:I60,I64:I74,I78:I88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C10)))" Range("J8:J18,J22 J32,J36:J46,J50:J60,J64:J74,J78:J88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C11)))" Range("K8:K18,K22 K32,K36:K46,K50:K60,K64:K74,K78:K88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C12)))" Range("L8:L18,L22 L32,L36:L46,L50:L60,L64:L74,L78:L88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C13)))" Range("M8:M18,M22 M32,M36:M46,M50:M60,M64:M74,M78:M88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C14)))" Range("N8:N18,N22 N32,N36:N46,N50:N60,N64:N74,N78:N88").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,(CONCATENATE(RC1,R4C15)))" Range("O8:O18,O22 O32,O36:O46,O50:O60,O64:O74,O78:O88").Select 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.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
-
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.
CodeCode
Display More[Sub Channel_input_formula() Range("E57,E8:P18,E22:P32,E36:P46,E50:P60,E64:P74,E78:P88").Select Selection.ClearContents Range("E8").Select ActiveCell.FormulaR1C1 = "=COUNTIF(LOOKUP2,CONCATENATE(RC1,R4C))" Range("E8").Select Selection.Copy Range("E9:E18,E22:E32,E36:E46,E50:E60,E64:E74,E78:E88").Select ActiveSheet.Paste Application.CutCopyMode = False Range("E64:E74").Select Range("E74").Activate Selection.AutoFill Destination:=Range("E64:P74"), Type:=xlFillDefault Range("E64:P74").Select Range("E50:E60").Select Range("E60").Activate Selection.AutoFill Destination:=Range("E50:P60"), Type:=xlFillDefault Range("E50:P60").Select Range("E36:E46").Select Range("E46").Activate Selection.AutoFill Destination:=Range("E36:P46"), Type:=xlFillDefault Range("E36:P46").Select Range("E22:E32").Select Range("E32").Activate Selection.AutoFill Destination:=Range("E22:P32"), Type:=xlFillDefault Range("E22:P32").Select Range("E8:E18").Select Range("E18").Activate Selection.AutoFill Destination:=Range("E8:P18"), Type:=xlFillDefault Range("E78:E88").Select Range("E88").Activate Selection.AutoFill Destination:=Range("E78:P88"), Type:=xlFillDefault End Sub]
Code ends
Thanks -
Re: Countif formula for multiple cells and sheets
I have already asked you in this thread to use code tags correctly. You have not taken note and have once again posted code with incorrect tags.
You must edit your post to correct this otherwise you post will be locked as will the other post you have just started. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!