Looping thru columns beyond Z

[John Pierce]

Public Sub Evaluate ()
Dim R As Single
Dim C As Integer
Range("i11").Select
For R = 11 To 15
For C = 9 To 108 '100 Columns

Cells(R, C).Value = _
Evaluate("=SUMPRODUCT(COUNTIF(" & Chr(C + 64) & "$2: _
" & Chr(C + 64) & "$7,$C" & R & ":$H" & R & "))")
Next C
Next R
End Sub

The procedure above gives correct results only out to Col Z.
After that, things get weird. I know it it because of the letter
column reference but I don't know how to fix it. The function
below is supposed to help but I don't know how to use it.

Private Function strColid(jColNo As Long) As String
' returns column name/id of colno
strWhere = "strColid"
'
If jColNo > 0 And jColNo <= 256 Then
If jColNo < 27 Then
strColid = Chr$(((jColNo - 1) Mod 26) + 65)
Else
strColid = Chr$(64 + Int((jColNo - 1) / 26)) &
Chr$(((jColNo - 1) Mod 26) + 65)
End If
Else
strColid = "#N/A"
End If
End Function

 

[acw]

Joh

Tr
Cells(R, C).Value = Evaluate("=sumproduct( countif( " & Cells(2, C).Address & ":" & Cells(7, C).Address & "," & Cells(R, 3).Address & ":" & Cells(R, 7).Address & ") ) "

Ton

----- John Pierce wrote: ----

Public Sub Evaluate (
Dim R As Singl
Dim C As Intege
Range("i11").Selec
For R = 11 To 15
For C = 9 To 108 '100 Column

Cells(R, C).Value =
Evaluate("=SUMPRODUCT(COUNTIF(" & Chr(C + 64) & "$2:
" & Chr(C + 64) & "$7,$C" & R & ":$H" & R & "))"
Next
Next
End Su

The procedure above gives correct results only out to Col Z
After that, things get weird. I know it it because of the lette
column reference but I don't know how to fix it. The functio
below is supposed to help but I don't know how to use it

Private Function strColid(jColNo As Long) As Strin
' returns column name/id of coln
strWhere = "strColid

If jColNo > 0 And jColNo <= 256 The
If jColNo < 27 The
strColid = Chr$(((jColNo - 1) Mod 26) + 65
Els
strColid = Chr$(64 + Int((jColNo - 1) / 26))
Chr$(((jColNo - 1) Mod 26) + 65
End I
Els
strColid = "#N/A
End I
End Functio

 

[Dave Peterson]

Instead of worrying about how to translate it, just let excel do the work.

Option Explicit

Public Sub Evaluate()
Dim R As Long
Dim C As Long

With ActiveSheet
'.Range("i11").Select
For R = 11 To 15
For C = 9 To 108 '100 Columns
.Cells(R, C).Value = _
Application.Evaluate("SUMPRODUCT(COUNTIF(" & _
.Range(.Cells(2, C), .Cells(7, C)).Address(external:=True) & _
"," & _
.Range(.Cells(R, "C"), .Cells(R, "H")).Address(external:=True) _
& "))")
Next C
Next R
End With
End Sub

 

[John Pierce]

Tony,
Thanks for the solution. It worked perfectly for me.

JohnSCPierce

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!