Trying to write a macro that will copy a formula - TIA

[JasonK]

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

 

[Dave Peterson]

with activesheet
.range("B1:B550").formula = "=countif(a:a,a1)"
end with

Excel will fill the range and adjust the formula for each cell--just like if you
selected the entire range, wrote the formula for the activecell and used
control-enter to fill the selection with that adjusted formula.

 

[JasonK]

Thank you Dave.

with activesheet
.range("B1:B550").formula = "=countif(a:a,a1)"
end with

Excel will fill the range and adjust the formula for each cell--just like if you
selected the entire range, wrote the formula for the activecell and used
control-enter to fill the selection with that adjusted formula.

 

[Sam Wilson]

For your column B formula you want

with range("a1")
for i = 0 to 549
.offset(i,1).formula = "=countif(A:A," &
..offset(i,0).address(false,true) & ")"
next i
end with

for copying formulas in from other sheets, is there any pattern to which
cells you want? ie, is it the same 50 cells from the 11 sheets in the
workbook that aren't the active sheet, and are they always in the same range?

 

[JasonK]

Thank you Sam. I appreciate your help.

jasonK

For your column B formula you want

with range("a1")
for i = 0 to 549
.offset(i,1).formula = "=countif(A:A," &
.offset(i,0).address(false,true) & ")"
next i
end with

for copying formulas in from other sheets, is there any pattern to which
cells you want? ie, is it the same 50 cells from the 11 sheets in the
workbook that aren't the active sheet, and are they always in the same range?