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.AutoFi t
End Sub





any help gratefully recieved. please contact for any further info


cheers


jb