I have a column of text only information in A1 thru A26, with a control
number in A27.
I want to copy A1 thru A26 to C1 and transpose that column (values only) to
that row.
Then I want to repeat that same copy (for the number of times that is
indicated in A27.
I would then like to go to the bottom of column C and and go down 4 rows
below the last row and put a formula that counts the numberof times that the
information in row 1 occurs in the rows below it.
And finally I would like to copy that formula across the rows for the
transposed information..

Each time I have tried to do this in a recorded macro I couldn't get it to
work, mostly because of the formula and the location of the bottom of the
row, which seems to go to the wrong place. I would appreciated any help and
thank you in advance.


Chuck C

See excel.worksheet.functions

"Chuck" wrote in message ...
I have a column of text only information in A1 thru A26, with a control
number in A27.
I want to copy A1 thru A26 to C1 and transpose that column (values only) to
that row.
Then I want to repeat that same copy (for the number of times that is
indicated in A27.
I would then like to go to the bottom of column C and and go down 4 rows
below the last row and put a formula that counts the numberof times that the
information in row 1 occurs in the rows below it.
And finally I would like to copy that formula across the rows for the
transposed information..

Each time I have tried to do this in a recorded macro I couldn't get it to
work, mostly because of the formula and the location of the bottom of the
row, which seems to go to the wrong place. I would appreciated any help and
thank you in advance.


Chuck C

chuck,
it appears to me that If you copy/transpose A1:A26 the number of times
indicated in A27 then the data in column C will not change, and therefore at
Cx the count of data in A1 will equal the value in A27.

But anyway:

Sub For_Chuck()
For counter = 1 To Range("A27").Value
Range("A1:A26").Copy
Range("C" & counter & ":AB" & counter).PasteSpecial Transpose:=True
Next counter

Range("C" & counter + 4).Select

Do Until ActiveCell.Column 28
For counter2 = 3 To 28
ActiveCell.FormulaR1C1 = "=countif(r1c" & counter2 & ":r" &
counter - 1 & "c" & counter2 & ",r" & counter2 - 2 & "c1)"
Next counter2
ActiveCell.Offset(0, 1).Select

Loop
End Sub


Steve


"Chuck" wrote in message
...
I have a column of text only information in A1 thru A26, with a control
number in A27.
I want to copy A1 thru A26 to C1 and transpose that column (values only)
to
that row.
Then I want to repeat that same copy (for the number of times that is
indicated in A27.
I would then like to go to the bottom of column C and and go down 4 rows
below the last row and put a formula that counts the numberof times that
the
information in row 1 occurs in the rows below it.
And finally I would like to copy that formula across the rows for the
transposed information..

Each time I have tried to do this in a recorded macro I couldn't get it to
work, mostly because of the formula and the location of the bottom of the
row, which seems to go to the wrong place. I would appreciated any help
and
thank you in advance.


Chuck C