Hi

I am trying to make a shortcut, but the bit iam missing is quite an
important technical bit that will help me elsewhere.

basically, the shortcut puts a countif formula next to the active cell when
the macro is run e.g. in B2 next to the data in A2. i want it to countif for
the entire range of the data in column A. Then i want to dollar up that range
to lock it & drop  the formula down the entire length of column B where there
is data in column A.

The problem i have is that even with the 'relative preference' button
ticked, the code still makes reference to specific cells or range lenghts /
sizes. I want this macro to work on data sets of any type. does anyone know
how to make this more transferable.

here is the basic code so far

ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveCell.FormulaR1C1 = "=COUNTIF(R2C8:R10208C8,RC[-1])"
   ActiveCell.Select
   Selection.AutoFill Destination:=ActiveCell.Range("A1:A10207")
   ActiveCell.Range("A1:A10207").Select
   ActiveCell.Columns("A:A").EntireColumn.Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
       :=False, Transpose:=False
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
   ActiveCell.Offset(1, -1).Range("A1").Select
   Application.CutCopyMode = False
   ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.EntireColumn.AutoFit
End Sub

any help gratefully recieved. please contact for any further info

cheers

jb