计数如果是excel VBA，并打印结果在另一个范围 [英] Count if for excel VBA, and printing results in another range

问题描述

我有一个约12,000行的列表，包括项目编号，客户经理，创建日期，状态...等等。
目前我每两周作为数据透视表进行报告，然后我画出来的图形。我知道这可以是自动化的，因为我消除了数据透视表，并将结果与​​countifs进行了复制。
现在我想要能够使用VBA做同样的事情，直到用户可以在电子表格中点击，点击一个按钮，最新的数据将被描绘出来。为了开始，我想在vba中探索一点点。

让我们说表格看起来像这样

  A | B | C 
 proj.Number客户经理状态
 123人1元
 234人2失去
 345人3引用
  

目前这是我的代码，这对于countif来说很好，但是它没有一个循环...而且我知道它可以以某种方式实现

  Dim PersonOne as Range 
 Set PersonOne = Range（E2）
 Dim PersonTwo as Range 
 Set PersonTwo = Range E3）
 Dim PersonThree as Range 
 Set PersonThree = Range（E4）
 
范围（D2）=人1
范围D3）=Person 2
 Range（D4）=Person 3
 
 PersonOne =（WorksheetFunction.CountIf（Range（B2，Range（B2 ）.End（xlDown）），Person 1））
 PersonTwo =（WorksheetFunction.CountIf（Range（B2，Range（B2）。End（xlDown）），Person 2）） 
 PersonThree =（WorksheetFunction.CountIf（Range（B2，Range（B2）。End（xlDown）），Person 3））
  

如何自动化这一点，甚至我甚至不必写出人物的名字（我所说的范围（d2）的部分=某人
我可以有一个寻找所有可能的唯一名称的代码，将它们放在电子表格的一定范围内，而不是计算给定范围内出现的名称的次数？

谢谢

解决方案

以下代码和功能应该做你所需要的。虽然它目前打印到列D和E在同一页，但你可以轻松地改变，如果你想要其他地方。

  Sub CountIF（）
 
 Dim wbk As Workbook 
 Dim ws As Worksheet 
 Dim myNames（）As String 
 Dim lRow As Long，x As Long 
 Dim Cell As Range 
 Dim Test As Boolean 
 
 Set wbk = Workbooks（将此更改为您的工作簿名称
设置ws = wbk.Worksheets（Sheet1）将其更改为工作表名称
 
 ReDim myNames（0至0）As String 
 
带有ws 
 lRow = .Range（B& .Rows.Count）.End（xlUp）.Row 
'循环通过列B并填充数组
对于每个单元格.Range（.Cells（2，B），.Cells（lRow，B））
'检查名称是否已经在数组
 Test = IsInArray Cell.Value，myNames）> -1 
如果Test = False然后
'将数组插入数组
 myNames（UBound（myNames））= Cell.Value 
 ReDim保留myNames（0到UBound（myNames）+ 1）As String 
 End If 
下一个单元格
 
 ReDim保留myNames（0到UBound（myNames） -  1）As String 
'在D中打印标题和值在E 
对于x = LBound（myNames）到UBound（myNames）
'使用x + 1，因为我们的数组从0开始
 .Cells（x + 1，D）值= myNames（x）
 .Cells（x + 1，E）。Value = WorksheetFunction.CountIF（.Range（.Cells（2，B），.Cells（lRow，B）） ，myNames（x））
下一个x 
结束
 
删除myNames 
 
 End Sub 
  / pre> 
 
 
代码使用此功能，所以一定要包括它
 函数IsInArray（stringToBeFound As String，arr as Variant）As Long 
'http://stackoverflow.com/q uestions / 10951687 / how-to-search-for-string-in-a-array 
'Boolean =（IsInArray（StringToFind，ArrayToSearch）> -1）
 Dim i As Long 
'如果在数组中找不到值，则返回值
 IsInArray = -1 
 
对于i = LBound（arr）到UBound （arr）
如果StrComp（stringToBeFound，arr（i），vbTextCompare）= 0然后
 IsInArray = i 
退出
结束如果
下一个i 
结束功能
  
 
I have a list of about 12,000 lines, with project numbers, account managers, create date, status... and so on.. 
Currently I am making reports every 2 weeks, as pivot tables, and then I make graphs out of them. I know that this can be automated, as I eliminated the pivot tables and replicated the result with countifs. 
Now I want to be able to do the same thing with VBA, to the point where a user can go in a spreadsheet, hit a button and the most current data will portray. To start with this, I want to explore a little bit of countif in vba. 

Let's say that the table looks like this 
 A          |         B       |    C
proj.Number   Account Manager   Status
   123            Person 1       Won
   234            Person 2       Lost
   345            Person 3       Quoted
Currently this is my code, that works fine for countif, but it's without a loop... and I know it can be done somehow
 Dim PersonOne as Range
    Set PersonOne = Range("E2")
 Dim PersonTwo as Range
    Set PersonTwo = Range("E3") 
 Dim PersonThree as Range
    Set PersonThree = Range("E4")

        Range("D2") = "Person 1"
        Range("D3") = "Person 2"
        Range("D4") = "Person 3"

PersonOne = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 1"))   
PersonTwo = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 2"))  
PersonThree = (WorksheetFunction.CountIf(Range("B2", Range("B2").End(xlDown)), "Person 3")) 
How do I automate this, to the point that I don't even have to write the names of the people (the part where I say range(d2) = some person 
Can I have a code that looks for all possible unique names, puts them in a certain range of a spreadsheet, and than count how many times that name occurs in the given range?

Thank you
解决方案
The below code and function should do what you need. Although it currently prints to column D and E on the same page but you can easily change that if you want it somewhere else. 
Sub CountIF()

    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim myNames() As String
    Dim lRow As Long, x As Long
    Dim Cell As Range
    Dim Test As Boolean

    Set wbk = Workbooks("Book1.xlsm") 'Change this to your workbook name
    Set ws = wbk.Worksheets("Sheet1") 'Change this to your worksheet name

    ReDim myNames(0 To 0) As String

    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        'Loop through Column B and populate array
        For Each Cell In .Range(.Cells(2, "B"), .Cells(lRow, "B"))
            'Check if Name is already in array
            Test = IsInArray(Cell.Value, myNames) > -1
            If Test = False Then
                'Insert name into array
                myNames(UBound(myNames)) = Cell.Value
                ReDim Preserve myNames(0 To UBound(myNames) + 1) As String
            End If
        Next Cell

        ReDim Preserve myNames(0 To UBound(myNames) - 1) As String
        'Print title in D and value in E
        For x = LBound(myNames) To UBound(myNames)
            'Use x + 1 because our array starts at 0
            .Cells(x + 1, "D").Value = myNames(x)
            .Cells(x + 1, "E").Value = WorksheetFunction.CountIF(.Range(.Cells(2, "B"), .Cells(lRow, "B")), myNames(x))
        Next x
    End With

    Erase myNames

End Sub
The code uses this function so be sure to include it
Function IsInArray(stringToBeFound As String, arr As Variant) As Long
'http://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array
'Boolean = (IsInArray(StringToFind, ArrayToSearch) > -1)
    Dim i As Long
    ' default return value if value not found in array
    IsInArray = -1

    For i = LBound(arr) To UBound(arr)
        If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then
            IsInArray = i
            Exit For
        End If
    Next i
End Function


                        
这篇关于计数如果是excel VBA，并打印结果在另一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！