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

--
Message posted from http://www.ExcelForum.com

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

might work nicely.


"paku <" wrote:

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.

dido

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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

--
Message posted from http://www.ExcelForum.com

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).


"paku <" wrote:

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!

dido

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson