counting number of occurrence of a value and depositing that number in another column

**TrainTrainer** · 11-21-2019, 02:43 PM

I have a spreadsheet that has a list of all errors made by our agents. Column B has an agent name. In column I, I need a total number of times the name appears in the sheet (this is the number of errors for that person). We are not removing duplicates because each row is a unique error. I just need to be able to look at each line and see the total number of errors that person made.

The data is dynamic and will change in length. I'm having difficulty incorporating the last row into the formula, but I need it to read what the last row is and stop calculating at that point. There is existing code prior to this point and I have already declared LR as last row and LC as last column in the prior code. I've tried setting a range and using that within the formula, but obviously i'm doing something wrong LOL

Assistance is appreciated; I'm still learning :-)

**paulked** · 11-21-2019, 04:51 PM

Post your code

**TrainTrainer** · 11-21-2019, 06:06 PM

This is where the code errors out:

Dim namerng As Range
Dim errorrng As Range
Set namerng = Range("$B$2:B" & LR)
Set errorrng = Range("I2:I" & LR)

Range("I1").Select
ActiveCell.FormulaR1C1 = "# of Errors"
Range("errorrng").Formula = "=countif(namerng, B2)" <----this is what messes up, it gives me a global fail message.
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & LR)

After I wrote this I did see a comment saying the countif cannot be used with a named dynamic range like that but I don't know if that's accurate.

I'm learning VBA via a course as I also try to learn by doing on the job so if that code is crap please excuse my clunkiness lol

**paulked** · 11-21-2019, 06:18 PM

After reading that did you try:

Range("errorrng").Formula = "=countif(Range("$B$2:B" & LR), B2)"

**paulked** · 11-21-2019, 06:23 PM

Just a point from your first post, you can have many last rows eg:

Sub test()
    Dim lr As Long, lr1 As Long, lr2 As Long 'etc...
    lr = Cells(Rows.Count, 1).End(xlUp).Row ' last row of column 1 ("A")
    lr1 = Cells(Rows.Count, 3).End(xlUp).Row ' last row of column 3 ("C")
    lr2 = Cells(Rows.Count, "AB").End(xlUp).Row ' last row of column 28 ("AB")
    ' etc...
End Sub

**TrainTrainer** · 11-21-2019, 06:40 PM

I tried this one, It tells me the "$" is an invalid character

**TrainTrainer** · 11-21-2019, 06:46 PM

when I run it, here's the errors

1.JPG 2.JPG 3.JPG

**paulked** · 11-21-2019, 07:28 PM

So did you remove the $'s?

Range("errorrng").Formula = "=countif(Range("B2:B" & LR), B2)"

**TrainTrainer** · 11-21-2019, 08:37 PM

yes, then it says 'expected end of statement'

**paulked** · 11-21-2019, 08:41 PM

Can you post the workbook?

**TrainTrainer** · 11-21-2019, 09:08 PM

can't post the workbook due to confidentiality but i can post the code

Sub addcolumn()

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("Raw Data").Select
Columns("A:F").EntireColumn.AutoFit
Columns("G:H").Select
Selection.ColumnWidth = 41.71
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Dim namerng As Range
Dim errorrng As Range
Set namerng = Range("$B$2:B" & LR)
Set errorrng = Range("I2:I" & LR)

Range("I1").Select
ActiveCell.Value = "# of Errors"
Range("errorrng").Formula = "=countif(namerng, B2)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & LR)
Range("A1:I1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Range("A1:I1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

**paulked** · 11-21-2019, 09:12 PM

Then post some sample data, I can't work blind!

**TrainTrainer** · 11-21-2019, 09:13 PM

this is what it looks like

Attachment 25469

so for # of errors, lines 2 and 7 would say "2" based on calculations that the agent shows up twice on this report. instead of person1 et al, the real report shows names

**TrainTrainer** · 11-21-2019, 09:14 PM

I was working on it....

**TrainTrainer** · 11-21-2019, 09:20 PM

OHR	SRT Name	FLM	Shift	job_id	is_correct	Agent Decision	Correct Decision
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 1	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 2	FLM	3. Overnight - 4x10	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 3	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH
0	Agent 4	FLM	1. Morning	#	FALSE	ABCD	EFGH

I made a test worksheet but couldnt figure out how to attach it here, but heres the data, it should copy and paste right into excel

**paulked** · 11-21-2019, 09:45 PM

Sub addcolumn()
    Dim lr As Long, lc As Long, i As Long
    Dim namerng As Range
    Dim errorrng As Range


    lr = Cells(Rows.Count, 1).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column




    Sheets("Raw Data").Select
        Columns("A:F").EntireColumn.AutoFit
    With Columns("G:H")
        .ColumnWidth = 41.71
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


    'Set namerng = Range("B2:B" & lr)
    'Set errorrng = Range("I2:I" & lr)


    Range("I1") = "# of Errors"
    For i = 2 To lr
        Cells(i, 9).Formula = "=countif(B2:B" & lr & ", B" & i & ")"
    Next
    'Range("I2").Select
    'Selection.AutoFill Destination:=Range("I2:I" & lr)
    Range("A1:I1").Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Range("A1:I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
        With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

**paulked** · 11-21-2019, 09:48 PM

I couldn't load your attachment but i tried the above on your data and it seemed ok.

Try to avoid Select and With Selection, it is really slow. I have given an example on how to avoid it above instead of:

Columns("G:H").Select
Selection.ColumnWidth = 41.71
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

use:

    With Columns("G:H")
        .ColumnWidth = 41.71
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

**TrainTrainer** · 11-21-2019, 10:00 PM

TY! TY! TY! It works great and now that I see what you did I know where I messed up.

Thanks for the tip on selection, also.

**paulked** · 11-21-2019, 10:03 PM

You're welcome

Thread: counting number of occurrence of a value and depositing that number in another column

Thread Tools

Display

counting number of occurrence of a value and depositing that number in another column

Posting Permissions