converting COUNTIFS formula to macro

Hi all

i have been setting up a macro with the kind help of a forum member the past week and it now works great.

I am wanting to add a formula to my worksheet which I want in the macro, but I am having a bit trouble putting it together. It is as follows:

 =COUNTIFS('g2 graded'!$G$1:$G$20000, C2, 'g2 graded'!$D$1:$D$20000, 1)

I tried this, which is similar to my countif formlas in the macro but it doesnt work.

 Cells(x, 20).Value = Application.WorksheetFunction.CountIfs(Sheets("g2 graded").Range("g1:g30000"), Cells(x, 3), Sheets("g2 graded").Range("d1:d30000"), "1""")

Any help would be greatly appreciated. I have clicked solved on my previous thread regarding this as I think it may be better aswered on this forum. This was the previous thread:


http://www.excelforum.com/excel-prog...-to-vba-2.html

thanks
gemma

this is the macro i want to use it in:

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