Have an alphbetical list need to count how many names start with A, B, C etc.

cadonn · #1 09-01-2014, 12:21 PM

Ignore my last post as it worked perfectly. I am now doing a bit of research on the code. I don't want to just take your code and run it - I would like to understand how it works. Thank you for providing this for me and starting me on the macro path

jolivanes · #2 09-01-2014, 03:39 PM

Good luck and make sure if you need anything for excel to come back here.

jolivanes · #3 09-02-2014, 08:30 AM

Cathy
The following line

Code:

Cells(j, 4).Formula = "=COUNTIF(RC[-2]:R[" & lr & "]C[-2],RC[-1] & ""*"")"    '<----- Column D has formula

needs to be changed to this

Code:

Cells(j, 4).Formula = "=COUNTIF(R1C[-2]:R[" & lr & "]C[-2],RC[-1] & ""*"")"    '<----- Column D has formula

It should be =COUNTIF(R1 instead of =COUNTIF(R
The 1 (one) needs to be there.
Good luck

jolivanes · #4 09-02-2014, 02:06 PM

Here is another possibility without the need for a Help Column.
In this code it assumes that Column C (3) is free. Change as required.

Code:

Sub One_More_Way()
    Dim j As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row    '<---- Column A (1) is being checked. Change as required
    For j = 1 To 26
        'The number 3 in Cells(j, 3) in the next line is Column C (3rd Column).Change as required.
        Cells(j, 3).Value = Application.WorksheetFunction.CountIf(Range("B1:B" & lr), _
        Chr(64 + j) & "*") & " x " & Chr(64 + j) & "'s"
    Next j
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Right Click menu list names	jimbassett	Word	0	02-25-2013 02:39 PM
How to import a list of names into PowerPoint from Excel?	CarpetRemnant	PowerPoint	4	12-07-2012 11:08 AM
auto insert names from list for printing	andreipopa2k	Word	1	12-09-2011 01:51 PM
Using a list of names in credits effect	Pemberton	PowerPoint	4	08-17-2010 02:10 AM
Random names from a given list	professor snape	Excel	1	06-06-2009 09:39 AM