Countif?

[cccbzg]

Hi,

I've got a spread sheet that gets updated with a macro to show a date and code. I need to count up all of the dates for each ID number generating the total field in col D. What complicates things for me is that there are an undetermined number of cols for each ID (row).

Many thanks for any help.

ID

fname

lname

total

date 1

code1

date2

code2

date3

code3

date4

code4

date5

code5

date6

code6

date7

code7

date8

code8

001

sam

smith

2

10/10/2015

VM

10/11/2015

HP

002

john

doe

1

09/01/2015

003

ben

franklin

3

09/02/2015

hist

09/08/2015

rre

09/12/2015

004

tom

jefferson

0

<tbody>
</tbody>

 

[Weazel]

since countif ignores blanks and text in the range argument you could just use something like

=COUNTIF(E2:BA2,">0")

I'd just make the range some obscenely high number to make sure you capture all the columns

i guess one caveat to this would be if any of the codes are numbers....

 

[svendiamond]

This code will fill column D ("total") with the desired results:

Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    Range("D2:D" & lastRow).ClearContents
    
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                datesFound = 0
                
        For colCounter = 5 To lastColumn Step 2
        
            If IsDate(Cells(rowCounter, colCounter)) Then
                datesFound = datesFound + 1
            End If
        
        Next colCounter
        
        Cells(rowCounter, 4).Value = datesFound
        
    Next rowCounter


MsgBox "complete"


End Sub

 

[svendiamond]

since countif ignores blanks and text in the range argument you could just use something like

=COUNTIF(E2:BA2,">0")

I'd just make the range some obscenely high number to make sure you capture all the columns

i guess one caveat to this would be if any of the codes are numbers....

Ha, much simpler and easier than what I offered. I'm just on here to practice my VBA skills. You could just use the formula cccbzg provided!

 

[Weazel]

thanks for the like Sven,

I like your code much better than the countif since it doesn't seem to matter if the code 1, code 2, etc. are text or numbers.

I really don't know VBA much so I'll have to dissect what you wrote.

very nice.

 

[svendiamond]

Here is the same code with some added comments for you (and OP) to understand what is going on

Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    
    'find the last row used
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    'clear column D
    Range("D2:D" & lastRow).ClearContents
    
    'for-next loop to go through each row
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
            
                'find last column used for each used row (each person, basically)
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                
                'start with 0
                datesFound = 0
                
        'for-next loop to go to the right through the columns
        For colCounter = 5 To lastColumn Step 2 'step 2 because every 2 columns is the date category
            
            'check if the value is a date
            If IsDate(Cells(rowCounter, colCounter)) Then
            
                'if so, add one to our datesFound variable
                datesFound = datesFound + 1
            End If
        
        'now go back up to the top of this loop, where it will add 2 to colCounter
        Next colCounter
        
        'once we've counted all the columns in that row that are dates, put that number in column D
        Cells(rowCounter, 4).Value = datesFound
        
    'go to the next row and do it all again
    Next rowCounter


MsgBox "complete"


End Sub

I know it can look daunting before you start learning VBA but I knew nothing about this a few months ago! A really great way to learn the basics is to watch WiseOwlTutorials on YouTube... he's really good at explaining this stuff. Here is a link: https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

 

[cccbzg]

Here is the same code with some added comments for you (and OP) to understand what is going on

Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    
    'find the last row used
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    'clear column D
    Range("D2:D" & lastRow).ClearContents
    
    'for-next loop to go through each row
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
            
                'find last column used for each used row (each person, basically)
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                
                'start with 0
                datesFound = 0
                
        'for-next loop to go to the right through the columns
        For colCounter = 5 To lastColumn Step 2 'step 2 because every 2 columns is the date category
            
            'check if the value is a date
            If IsDate(Cells(rowCounter, colCounter)) Then
            
                'if so, add one to our datesFound variable
                datesFound = datesFound + 1
            End If
        
        'now go back up to the top of this loop, where it will add 2 to colCounter
        Next colCounter
        
        'once we've counted all the columns in that row that are dates, put that number in column D
        Cells(rowCounter, 4).Value = datesFound
        
    'go to the next row and do it all again
    Next rowCounter


MsgBox "complete"


End Sub

I know it can look daunting before you start learning VBA but I knew nothing about this a few months ago! A really great way to learn the basics is to watch WiseOwlTutorials on YouTube... he's really good at explaining this stuff. Here is a link: https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

Thanks SO very much!! This works great!

 

[cccbzg]

I love this approach...how simple!! Thanks very much!