check the attachement see if it helps On Sun, May 15, 2011 at 1:22 PM, gemmamckinley <gmckin...@sky.com> wrote:
> Hi all > > I am trying to add a countifs formula to a running macro, but so far I > have not had much success and wonder if anyone could help pleas. I have > posted my query on another website without reply, and I will make sure I > post this link on there so people are aware I have asked on more than one > website. I am sure it is something simple I am missing but it is starting to > get frustrating so need some help. > > This is the formula I have set up, but as it is on many cells it is slowing > the workbook down. > [code]* > > =COUNTIFS('g2 graded'!$G$1:$G$20000, C2, 'g2 graded'!$D$1:$D$20000, 1) > > *[/code] > * > I tried this, which is similar to my countif formulas in the macro but it > doesnt work.* > > [code] > * > > Cells(x, 20).Value = Application.WorksheetFunction.CountIfs(Sheets("g2 > graded").Range("g1:g30000"), Cells(x, 3), Sheets("g2 > graded").Range("d1:d30000"), "1""") > > *[/code] > > My full running macro is: > > [code] > * > > Sub UpdateValues() > Application.ScreenUpdating = False > Dim cell As Range > For x = 2 To 10000 'loop through rows from row 2 to the last row > 'If cell isn't blue then macro assumes it doesn't need to continue (because > it has reached the end of the blue cells) > If Cells(x, 18).Interior.ColorIndex <> 37 Then > End > Else > 'insert value for column U (G2 Graded May). > 'note that we don't calculate value for columns R & S until last because they > depend on the calculated values of other columns > Cells(x, 21).Value = Application.WorksheetFunction.CountIf(Sheets("g2 > graded").Range("G1:G10000"), Cells(x, 3).Value) 'x refers to the current row > in the loop and 21 is the column number > 'insert value for column V (G2 Graded May). > Cells(x, 22).Value = Application.WorksheetFunction.SumIf(Sheets("g2 > graded").Range("G1:G10000"), Cells(x, 3).Value, Sheets("g2 > graded").Range("N1:N10000")) > 'insert value for column W (G2 GradedTotal). > Cells(x, 23).Value = Application.WorksheetFunction.CountIf(Sheets("g2 graded > april ").Range("H1:H10000"), Cells(x, 3).Value) + Cells(x, 21).Value 'x > refers to the current row in the loop and 21 is the column number > 'insert value for column X (G2 GradedTotal). > Cells(x, 24).Value = Application.WorksheetFunction.SumIf(Sheets("g2 graded > april ").Range("H1:H10000"), Cells(x, 3).Value, Sheets("g2 graded april > ").Range("O1:O10000")) + Cells(x, 22).Value > 'insert value for column Z (G2 Claim May). > Cells(x, 26).Value = Application.WorksheetFunction.CountIf(Sheets("g2 > claiming").Range("F1:F10000"), Cells(x, 3).Value) > 'insert value for column AA (G2 Claim May). > Cells(x, 27).Value = Application.WorksheetFunction.SumIf(Sheets("g2 > claiming").Range("F1:F10000"), Cells(x, 3).Value, Sheets("g2 > claiming").Range("AA1:AA10000")) > 'insert value for column AB (G2 Claim Total). > Cells(x, 28).Value = Application.WorksheetFunction.CountIf(Sheets("g2 > claiming april").Range("G1:G10000"), Cells(x, 3).Value) > 'insert value for column AC (G2 Claim Total). > Cells(x, 29).Value = Application.WorksheetFunction.SumIf(Sheets("g2 claiming > april").Range("G1:G10000"), Cells(x, 3).Value, Sheets("g2 claiming > april").Range("AB1:AB10000")) > 'insert value for column R (Graded Total). > Cells(x, 18).Value = Cells(x, 23).Value + Cells(x, 28).Value > 'insert value for column S (Claiming Total). > Cells(x, 19).Value = Cells(x, 24).Value + Cells(x, 29).Value > End If > Next > Application.ScreenUpdating = True > End Sub > > * > [/code] > > The webpage it is on is > http://www.excelforum.com/excel-worksheet-functions/776036-converting-countifs-formula-to-macro.html > > Any help would be greatly appreciated. > > Regards > Gemma > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
COUNTIFS.xlsm
Description: Binary data