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$2, C2, 'g2 graded'!$D$1:$D$2, 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:g3), Cells(x, 3), Sheets(g2
graded).Range(d1:d3), 1)
*[/code]
My full running macro is:
[code]
*
Sub UpdateValues()
Application.ScreenUpdating = False
Dim cell As Range
For x = 2 To 1 '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:G1), 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:G1), Cells(x, 3).Value, Sheets(g2
graded).Range(N1:N1))
'insert value for column W (G2 GradedTotal).
Cells(x, 23).Value = Application.WorksheetFunction.CountIf(Sheets(g2 graded
april ).Range(H1:H1), 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:H1), Cells(x, 3).Value, Sheets(g2 graded april
).Range(O1:O1)) + Cells(x, 22).Value
'insert value for column Z (G2 Claim May).
Cells(x, 26).Value = Application.WorksheetFunction.CountIf(Sheets(g2
claiming).Range(F1:F1), Cells(x, 3).Value)
'insert value for column AA (G2 Claim May).
Cells(x, 27).Value = Application.WorksheetFunction.SumIf(Sheets(g2
claiming).Range(F1:F1), Cells(x, 3).Value, Sheets(g2
claiming).Range(AA1:AA1))
'insert value for column AB (G2 Claim Total).
Cells(x, 28).Value = Application.WorksheetFunction.CountIf(Sheets(g2
claiming april).Range(G1:G1), 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:G1), Cells(x, 3).Value, Sheets(g2 claiming
april).Range(AB1:AB1))
'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
