I wrote a macro to copy data from several columns in several sheets to
one column on a new sheet. That data is in range A1:A550 on the active
sheet.

While still in the same macro, I need to place a formula in the cell
to the right of each cell in A that will count the number of times the
text data appears in column A.

I need B1 to be =countif(a:a,a1)
and B2 to be =countif(a:a,a2) , etc...

I was trying to use a for/next loop to get the formula in each cell
but I don't know the correct syntax.

for i = 1 to 550
cells(i,2)=(=countif(a:a,cells(i,1)))
next i

There is no other data in A so a macro line that would place the
formula to the right of any cell containing data in A would be fine.
In other words, I don't have to use a for/next loop to do this if some
other way will work.


Also -- when i copy the selection of 50 in the macro and attempt to
paste that particular selection into the new sheet, i cannot make the
paste happen unless the new range is the same size. When I copy and
paste outside of a macro, just using copy and paste, the size of the
range of copy (in this case the 50 cells) will paste using the active
cell as the first cell in the range. How can I do that inside a macro?
Right now I'm using this format in the macro to copy and paste:

Worksheets("DP").Range("af5:af54").Copy
Worksheets("Complete List").Range("a501:a550").PasteSpecial

I can't make it paste any other way. I would like to make it add 50 to
a counter and paste the data in a cell (counter,1)


I appreciate your help. I'm a rank amateur at this, but I'm learning.

Thanks again,
JasonK