detect the letters in the group and add their quantities forming the suit
- Thread starter jack
- Start date
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
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
Frank
Member
I know I'm going to regret replying, I always do, but this is my ONE reply to answer the question. As usual its using a sledgehammer to crack a nut by using a macro for this.
Pretty much the same formulas Ive been showing you for years could have done it, The hardest job was putting the Xs in the grid and the macro wasn't doing that!
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.The macro depended on counting up how many rows of Xs there were in order to work properly. Ive changed it to counting up how many RESULTS there are.
It doesn't do the SUM part either, but its always 7 so you don't need it.
https://www.mediafire.com/file/5dmv6b3bvzvdavv/somar_ase_detectar_as_letras.xlsm/file.
Pretty much the same formulas Ive been showing you for years could have done it, The hardest job was putting the Xs in the grid and the macro wasn't doing that!
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.The macro depended on counting up how many rows of Xs there were in order to work properly. Ive changed it to counting up how many RESULTS there are.
It doesn't do the SUM part either, but its always 7 so you don't need it.
https://www.mediafire.com/file/5dmv6b3bvzvdavv/somar_ase_detectar_as_letras.xlsm/file.