Loop through a column

k7dm · #1 09-22-2016, 12:50 PM

I have a ten-column worksheet and each column is a list of ham radio call signs that is sorted alphabetically A to Z. Each of the calls may be repeated any number of times and there are a total of 384 entries in each column. I’d like to be able to loop through the columns and count the number of times a given call appears in a column using the COUNTIF function. A sample column is shown in the attached Excel 2007 file. I’d very much appreciate any help on this.
Thanks.
Don, K7DM

jeffreybrown · #2 09-22-2016, 03:57 PM

Hi Don,

Let's start with this.

=IF(A1<>A2,COUNTIF($A$2:$A$14,A2),"")

I'm guessing you are looking for a macro? You show in your file data starting in row 1, but is row 1 a header row? It would make things a little easier maybe as the formula above starts in B2.

Without seeing the actual full layout, I had to take a guess on your layout. Just press the button and tell me what you think.

k7dm · #3 09-22-2016, 06:27 PM

Hi Jeff,

Thanks for your reply. Attached is an updated Excel file with four columns, all of which have headers now. Is the code you sent a macro andif so, how do I execute it?

Thanks again.

Don

jeffreybrown · #4 09-22-2016, 07:21 PM

Yes, this is a macro which will apply the Countif formula to each of the four columns (B, D, F, and H). You initially descried what you want as 10 columns with an unknown row number, but you sample is 4 columns with the same number of rows. I realize it is a sample, but the reason I mention it, the code could be written slightly different for all different types of scenarios.

In the end, if you do not want a macro, the formula by itself will suffice. This macro creates the Countif formula, but then copies it as values instead of leaving the formula intact.

If you want to just use a formula, paste into B2 and copy down...

=IF(A1<>A2,COUNTIF(A$2:A$14,A2),"")

...now apply to the other columns, but again, this is only if the row numbers are the same. If they are not, just adjust A$14 to the length of your rows.

Code:

Sub ApplyCountif()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    
    Const x As Long = -1
    Const sFormula As String = "=IF(R[" & x & "]C[" & x & "]<>RC[" & x & "],COUNTIF(C[" & x & "],RC[" & x & "]),"""")"
    
    For i = 1 To LastCol Step 2

        LastRow = Cells(Rows.Count, i).End(xlUp).Row - 1

        Application.ScreenUpdating = False

        With Cells(2, i + 1).Resize(LastRow)
            .Formula = sFormula
            .Value = .Value
        End With

    Next i

    Application.ScreenUpdating = True

End Sub

Paste code in a Normal module

Where to paste code
Highlight macro to copy >> Ctrl + C >> Open your workbook
Alt + F11 >> opens the Visual Basic Editor (VBE)
Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
Paste code >> Ctrl + V (right side of screen)
Alt + Q >> exits VBE and returns to Excel
Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

k7dm · #5 09-22-2016, 08:46 PM

Hi Jeff,

Thanks very much. This works perfectly. I'll make a full size sheet and test it tomorrow. BTW, the ten columns are the results of a vote for ten different awards our club has. I use a web site called Simply Voting and it provides results in raw data in Excel and a certified PDF file. There are at present 338 members who are eligible to vote. I'll probably bug you with questions about your code. Right now I don't understand it any more than I would trying to read a book written in Mandarin Chinese.

Thanks again.

Don

jeffreybrown · #6 09-23-2016, 04:30 AM

Hi Don,

You are very welcome.

k7dm · #7 09-23-2016, 11:40 AM

Hi Jeff,

As I feared, there's a lot I don't understand about the code. What does "x" do in the line that sets sFormula? The With thing is a mystery to me, even though I found some examples of its use on the net. Why is .Value set to itself? I forgot to mention one important point. Columns ending in "1" are weighted, meaning that the calls therein get 2 points. Calls in columns ending in "2" get 1 point. So, the value that COUNTIF has for a given call in columns B, F, J, N, and Q should be multiplied by 2. I can't determine whether this can be done where sFormula is set or within the With statement. Again, thanks very much for your help. This will make my job much easier.

Don

jeffreybrown · #8 09-23-2016, 02:53 PM

Hi Don,

Trust me, there's enough code I don't understand either, but getting good enough to craft something together to get me by.

This x is a variable which is initially set to -1. I basically means, from the active cell, -1.

Therefore, as we place the first Countif in column B, if you -1, the Countif is pointing to column A.

The .Value = .Value is basically taking all values in B2:B21 and making them values.

Code:

Sub ApplyCountif()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    Dim RPosition As Long
    Dim rngData As Range

    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    Const x As Long = -1
    Const sFormula As String = "=IF(R[" & x & "]C[" & x & "]<>RC[" & x & "],COUNTIF(C[" & x & "],RC[" & x & "]),"""")"

    For i = 1 To LastCol Step 2

        LastRow = Cells(Rows.Count, i).End(xlUp).Row - 1
        RPosition = InStr(1, Cells(1, i), 1)

        Application.ScreenUpdating = False

        With Cells(2, i + 1).Resize(LastRow)
            .Formula = sFormula
            .Value = .Value
            If RPosition <> 0 Then
                Set rngData = Cells(2, i + 1).Resize(LastRow)
                rngData = Evaluate(rngData.Address & "*2")
                .NumberFormat = "0;;;"
            End If
        End With

    Next i

    Application.ScreenUpdating = True

End Sub

No doubt somebody else could've or would've coded this requirement different, but if it achieve the desired result, the only thing left is speed.

To get a better picture of what the macro is doing, Step through the code using F8. You might also want to read up on debugging code or setting watches.

http://www.mrexcel.com/forum/lounge-...cks-gurus.html

http://www.excelforum.com/the-water-...hers-wont.html

http://www.mrexcel.com/forum/general...lications.html

k7dm · #9 09-25-2016, 07:01 AM

Hi Again,

This is exactly what I needed and it works perfectly. I wish I understood it better, but I'll go over it more thoroughly in the next day or so. I did a custom sort on each set of two columns so the call with the highest number is listed first. I just did that manually for each set of two columns and my goal is to add this to your code.

Thanks once again for all your help. BTW, is there a book on Excel VBA (or is it VBA Excel) you can recommend?

Don

jeffreybrown · #10 09-25-2016, 11:32 AM

Unfortunately I can't recommend any books as I have not read any. All my hard knocks have come from many many hours surfing thru multiple forums. One day when you get to the point where instead of asking the questions; but rather, answering the questions, that's when the knowledge base builds.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Timeline - loop through all dates between first and last given and add date to column if not found	carlos_cs	Excel Programming	1	05-03-2016 09:25 AM
documents saved with double column revert to single column when re-opened	danw	Word	6	04-08-2015 06:24 AM
Embedded Excel in Powerpoint - custom animation, display column by column	andytheanimal	PowerPoint	2	01-20-2015 06:30 AM
How can I temporarily break a 3 column format in order to type a single column paragraph	William P	Word	1	01-04-2015 06:40 PM
Want a quotient using a constant to show up in column c each time a number is added to column b	fibbermcghee	Excel	2	12-09-2014 05:48 PM