detect and add the quantities of letters in their groups

[jorel]

detect and add the quantities of letters in their groups
https://www.excelforum.com/attachmen...1&d=1551180540

[Andy Pope]

Does it need to be VBA?

AW6: =COUNTIFS($I$4:$AU$4,AW$4,$I6:$AU6,"X")

[jorel]

Does it need to be VBA? YES!please

[Andy Pope]

one of many ways to do it

Sub X()

    Dim header As Range
    Dim data As Range
    Dim output As Range
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim letterIndex As Long
    Dim letter As String
    Dim tally As Long
    
    Set header = Range("I4:AU4")
    Set data = Range("I6:AU7")
    Set output = Range("AW6")
    
    For rowIndex = 1 To data.Rows.Count
        For letterIndex = 1 To 4
            tally = 0
            letter = Chr(64 + letterIndex)
            For colIndex = 1 To data.Columns.Count
                If header.Cells(1, colIndex) = letter Then
                    If Len(data.Cells(rowIndex, colIndex)) > 0 Then
                        tally = tally + 1
                    End If
                End If
            Next
            output = tally
            Set output = output.Offset(0, 1)
        Next
        Set output = output.Offset(1, -4)
    Next
                
End Sub

[jorel]

I put it in the worksheet, but it is not running! can you see why it is not performing?

[Andy Pope]

Why did you put the code in the worksheet module rather than a standard module?

What error did you get?

Post example of what you have done

[jorel]

I put the macro in visual basic (modulo) gave the command execute, and nothing happens

[Andy Pope]

As you don't want to show me yours I will show you mine.

Run the macro X to populate cells.

[jorel]

hello Andy has something strange in the macro, I did the correct procedures
* the macro is not happening at all, it's not running, it's not doing anything, please !!

[Andy Pope]

Did you enable macros?

Try clearing the output cells before running the code so you can see the new output.

[jorel]

hello, ANDY POPE! I cleaned the cells, I did the procedures, but this one is running, can you do it on the planilia sent in the post? please

[Andy Pope]

I did use the file you posted. I simply copied the code into it and saved with the thread number and .xlsm file format

[jorel]

hello ANDY !!!maybe it's a misunderstanding
* the amcro has to do on all lines
* those two are just an example
and more every week I'll post new drawings
* the macro has to do all blank, it's not doing this

[Andy Pope]

Not so much a misunderstanding as you never mentioned the amount of 'X' would change.

Sub X()

    Dim header As Range
    Dim data As Range
    Dim output As Range
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim letterIndex As Long
    Dim letter As String
    Dim tally As Long
    
    Set header = Range("I4:AU4") ' assume fixed location
    Set data = Range("I6:AU6")
    Set output = Range("AW6")
    
    Do While Application.CountA(data) > 0
        For letterIndex = 1 To 4
            tally = 0
            letter = Chr(64 + letterIndex)
            For colIndex = 1 To data.Columns.Count
                If header.Cells(1, colIndex) = letter Then
                    If Len(data.Cells(rowIndex, colIndex)) > 0 Then
                        tally = tally + 1
                    End If
                End If
            Next
            output = tally
            Set output = output.Offset(0, 1)
        Next
        Set output = output.Offset(1, -4)   ' move reference down a row
        Set data = data.Offset(1, 0)        ' move reference down a row
    Loop
                
End Sub

[jorel]

the sum of the suit does not work,
* the macro does not work, put it on the sheet
no excel

[Andy Pope]

Sum and correction

Sub X()

    Dim header As Range
    Dim data As Range
    Dim output As Range
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim letterIndex As Long
    Dim letter As String
    Dim tally As Long
    Dim total As Long
    
    Set header = Range("I4:AU4") ' assume fixed location
    Set data = Range("I6:AU6")
    Set output = Range("AW6")
    
    Do While Application.CountA(data) > 0
        total = 0
        For letterIndex = 1 To 4
            tally = 0
            letter = Chr(64 + letterIndex)
            For colIndex = 1 To data.Columns.Count
                If header.Cells(1, colIndex) = letter Then
                    If Len(data.Cells(1, colIndex)) > 0 Then
                        tally = tally + 1
                    End If
                End If
            Next
            output = tally
            total = total + tally
            Set output = output.Offset(0, 1)
        Next
        output.Offset(0, 1) = total
        Set output = output.Offset(1, -4)   ' move reference down a row
        Set data = data.Offset(1, 0)        ' move reference down a row
    Loop
                
End Sub

[jorel]

HELLO ANDY=It didn't work because it classed the X's as data, not the actual results. They were not all filled in for all the draws, so it stopped after 2 rows.
I had to do formulas to put the Xs in.

[jorel]

andy , andy!!It did not work because it classified the X as data, not actual results. They were not all filled for all raffles, so they stopped after 2 lines.
* You have to make formulas to put the Xs. The macro depended on counting how many rows of X there were to work correctly you have to change to count how many RESULTS there are.

Nor does the SUM part see this

[Andy Pope]

Sub X()

    Dim header As Range
    Dim data As Range
    Dim output As Range
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim letterIndex As Long
    Dim letter As String
    Dim tally As Long
    Dim total As Long
    Dim drawData As Range
    Dim drawDataRow As Range
    Dim numberData As Range
    Dim xpos As Variant
    
    Set drawData = Range("A6").CurrentRegion
    
    Set header = Range("I4:AU4") ' assume fixed location
    Set numberData = Range("I5:AU5") ' assume fixed location
    Set data = Range("I6:AU6")
    Set output = Range("AW6")
    
    For Each drawDataRow In drawData.Rows
        
        For colIndex = 1 To drawDataRow.Columns.Count
            xpos = Application.Match(drawDataRow.Cells(1, colIndex), numberData, 0)
            data.Cells(1, xpos) = "X"
        Next
        
        total = 0
        For letterIndex = 1 To 4
            tally = 0
            letter = Chr(64 + letterIndex)
            For colIndex = 1 To data.Columns.Count
                If header.Cells(1, colIndex) = letter Then
                    If Len(data.Cells(1, colIndex)) > 0 Then
                        tally = tally + 1
                    End If
                End If
            Next
            output = tally
            total = total + tally
            Set output = output.Offset(0, 1)
        Next
        output.Offset(0, 1) = total
        Set output = output.Offset(1, -4)   ' move reference down a row
        Set data = data.Offset(1, 0)        ' move reference down a row
    Next
                
End Sub

[jorel]

huu andy, perfect, magnificent work,
* Correct, now it's working,
thank you!