Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to write a macro that will copy a formula - TIA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to write a macro that will copy a formula - TIA
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to write a macro that will copy a formula - TIA
Thank you Dave.
On Tue, 14 Jul 2009 09:47:03 -0500, Dave Peterson wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to write a macro that will copy a formula - TIA
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to write a macro that will copy a formula - TIA
Thank you Sam. I appreciate your help. jasonK On Tue, 14 Jul 2009 07:56:01 -0700, Sam Wilson wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
write a copy/paste from one workbook to another in a macro | Excel Discussion (Misc queries) | |||
How to write a function or macro to copy | Excel Programming | |||
How to write a function or macro to copy | Excel Programming | |||
how do I write macro to copy the value of a cell to another if va. | Excel Programming | |||
Write a macro ro copy column at certain time of day. | Excel Programming |