vba create summary on same sheet. I was wondering if anyone knew of a VBA code t

Question

vba create summary on same sheet.

I was wondering if anyone knew of a VBA code that looks at the data in a wworksheet and can more less do a countif function on the same sheet that contains the data. The following is my data::

         A                        B

Number               Dept

      1                      2505

       2                      2506

       3                      2905

       4                      2506

Now what I would like to do is use a VBA code to find the last row that contains this data, skip a row and then create a list of each department(only once) and then count how many times each department is in the above data. Basically making a data table or summary table. The result should look like the following..

Dept                    Issued

2505                         1

2506                         2

2905                         1

Thanks in advance for any help !

Explanation / Answer

Hi,

I noticed my mistake and reposted my code that counts instead of summing, here it is again

Sub Summarise()
Dim Lastrow As Long, x As Long.
Dim FirstRow As Long
Dim MyRange, c As Ra.nge.
Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
FirstRow = Lastrow + 2
Set MyRange = Range("B2:B" & Lastrow).
Cells(FirstRow, 1) = Cells(1, 1)
Cells(FirstRow, 2) = Cells(1, 2)
For Each c In MyRange
    If IsError(Application.Match(c.Value, _
    Range("A" & FirstRow).Resize(x + 1), 0)) Then
    x = x + 1
    Range("A" & FirstRow + x).Value = c.Value
    Range("A" & FirstRow + x).Offset(, 1).Value = _
    WorksheetFunction.CountIf(MyRange, c.Value)
    End If.
Next
End Sub.

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.