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
