excel vba - COUNTIF syntax

[paku]

Hi All,

I got a question on COUNTIF.

'keylength = CInt(KeyLengthComboBox.Value)

keylength = 3
myCol = 0

For x = 1 To keylength
For y = 1 to 26
Sheet10.Cells(y, x) = "=COUNTIF(sheet9!$A:$A,CHAR(66))"
Next y
Next x


Is it possible to replace the $A:$A part to a variable x which
represents the whole column 1 to 3 respectively and
also replace the CHAR(66) to CHAR(64+y)?

If it's possible, what is the correct syntax for doing it?

Many thanks.

did

 

[Dave Peterson]

Sheet10.Cells(y, x) = "=COUNTIF(sheet9!$A:$A,CHAR(" & 64 + y & "))"

might work nicely.

 

[paku]

thanks, dave but how about the $A:$A?
can it be replaced with variable x?
i want it to count every occurence in WHOLE column of x


e.g.Sheet10.Cells(y, x) = "=COUNTIF(sheet9!$A:$A,CHAR(" & 64 + y
"))"

thanks!

did

 

[Dave Peterson]

Oops. Missed that part. There are lots of ways. I like to set a range
variable to the range and let excel worry about the syntax (in the cell itself):


Option Explicit
Sub testme()

Dim y As Long
Dim x As Long
Dim myRng As Range

x = 1
y = 1

Set myRng = Sheet9.Columns(x)

Sheet10.Cells(y, x) = "=COUNTIF(" & myRng.Address(external:=True) _
& ",CHAR(" & 64 + y &
"))"

End Sub

Then I don't have to worry about someone changing the name of the worksheet,
too! (Not many know how to modify the codename (sheet10). But lots could
change that worksheet name in the tab (sheet9).