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