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
