# Macro To Look At Column C, Count Matching Cells and Put In Column D

#### Dazzawm

##### Well-known Member
I have workbooks of 100 of thousands of rows and I need a code please that will count the number of matching cells in column D and put a running count in column D please.

Please note it has to be a code rather than a formula. Thanks

Before

Excel 2010
CD
978AUA520D 1030
979AUA520D 1030
980AUA520D 1030
981AUA520D 1035
982AUA520D 1035
983AUA520D 1035
984AUA520D 1035
985AUA520D 1035
986AUA520D 1035

<tbody>
</tbody>
Sheet1

After

Excel 2010
CD
978AUA520D 10301
979AUA520D 10302
980AUA520D 10303
981AUA520D 10351
982AUA520D 10352
983AUA520D 10353
984AUA520D 10354
985AUA520D 10355
986AUA520D 10356

<tbody>
</tbody>
Sheet1

Last edited:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Akuini

##### Well-known Member
Try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1092265a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1092265-macro-look-column-c-count-matching-cells-put-column-d.html[/COLOR][/I]
[I][COLOR=seagreen]'' section group looping[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va
va = Range([COLOR=brown]"C1"[/COLOR], Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
j = i
[COLOR=Royalblue]Do[/COLOR]
i = i + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
[COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])

i = i - [COLOR=crimson]1[/COLOR]:    n = [COLOR=crimson]0[/COLOR]
[COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
n = n + [COLOR=crimson]1[/COLOR]:  va(k, [COLOR=crimson]1[/COLOR]) = n
[COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"D1"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR]) = va

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]``````

Thanks

You're welcome.

Replies
1
Views
34
Replies
0
Views
28
Replies
2
Views
310
Replies
1
Views
206
Replies
3
Views
246