View Full Version : Solved: countif function
sukumar.vb
08-28-2011, 06:02 AM
How to use Excel countif function in VBA?
:bug:
Not as an Excel Formula... so that User Defined Function does not appear on Worksheet, but posts only calculated values on Worksheet.
Refer attached file, where I want VBA to do calculations on Sheet1
:( :help
Bob Phillips
08-28-2011, 06:58 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, j
With Worksheets("Sheet1")
For j = 3 To .Range("B1").End(xlToRight).Column
For i = 2 To .Range("A2").End(xlDown).Row
.Cells(i, j).Formula = "=SUMPRODUCT(--(Sheet2!A2:A200=" & .Cells(i, "A").Address & "),--(Sheet2!B2:B200=" & .Cells(1, j).Address & "))"
.Cells(i, j).Value = .Cells(i, j).Value
Next i
Next j
End With
End Sub
sukumar.vb
08-28-2011, 03:42 PM
Requesting you to review this code again and ...Please advice SOP.
It might occur when the selection changes on a worksheet.
Can it not happen with Workbook Open event?
Please review attached file here and help me know how to put counts in yellow and Green cells of Sheet1.
Would Pivot table be an idea? :mkay I am looking to use different. :eek:
sukumar.vb
08-28-2011, 06:04 PM
:hi:
The was Dude's program, I have ever seen. Excellent.
I made some changes in below attachment.
This attachment works, but it needs another "FOR NEXT loop" to calculate Total for each row and col.
I also read your SOP about SUMPRODUCT. G8 Job done.
Thus, awaiting your response to close this thread in your praise.
:clap:
Bob Phillips
08-29-2011, 02:47 AM
Private Sub Workbook_Open()
Dim i As Long, j As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Activate
For j = 2 To .Range("B1").End(xlToRight).Column - 1
For i = 2 To .Range("A2").End(xlDown).Row - 1
.Cells(i, j).Formula = "=SUMPRODUCT(--(Sheet2!A2:A200=" & .Cells(i, "A").Address & "),--(Sheet2!B2:B200=" & .Cells(1, j).Address & "))"
.Cells(i, j).Value = .Cells(i, j).Value
Next i
Next j
.Cells(2, .Range("B1").End(xlToRight).Column).Resize(.Range("A2").End(xlDown).Row - 2).FormulaR1C1 = "=SUM(RC2:RC[-1])"
.Cells(.Range("A2").End(xlDown).Row, 2).Resize(, .Range("B1").End(xlToRight).Column - 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
End With
Application.ScreenUpdating = True
End Sub
Bob Phillips
08-29-2011, 02:50 AM
And yes, a pivot would work great.
sukumar.vb
08-30-2011, 11:47 AM
You are really distinguished member of VBAX.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.