Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run formula only on cells with data
I put in the range A7:A5000 so that I made sure the formula ran for all of my
cells, but it is putting in a blank value in all cells below the used rows. Each file I run this macro on will include a different number of rows. So, I need to paste the forumla only on rows that have data (are not empty). For instance, select rows with data, and then paste formula. Right now I have... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy Range("A7:A5000").Select ActiveSheet.Paste What happens is that when I print the file, it is trying to print rows 1 - 5000!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run formula only on cells with data
set last row of data:
lastrow=CELLS(rows.count,"A").end(xlup).row .... last row in Column A then Range("A7:A" & lastrow) .....?? HTH "Rob" wrote: I put in the range A7:A5000 so that I made sure the formula ran for all of my cells, but it is putting in a blank value in all cells below the used rows. Each file I run this macro on will include a different number of rows. So, I need to paste the forumla only on rows that have data (are not empty). For instance, select rows with data, and then paste formula. Right now I have... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy Range("A7:A5000").Select ActiveSheet.Paste What happens is that when I print the file, it is trying to print rows 1 - 5000!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run formula only on cells with data
be patient with me...
do you mean I should use your suggestion when I select rows where I want to paste the formula? For instance... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy lastrow = Cells(Rows.Count, "A").End(xlUp).Row Range ("A7:A" & lastrow) "Toppers" wrote: set last row of data: lastrow=CELLS(rows.count,"A").end(xlup).row .... last row in Column A then Range("A7:A" & lastrow) .....?? HTH "Rob" wrote: I put in the range A7:A5000 so that I made sure the formula ran for all of my cells, but it is putting in a blank value in all cells below the used rows. Each file I run this macro on will include a different number of rows. So, I need to paste the forumla only on rows that have data (are not empty). For instance, select rows with data, and then paste formula. Right now I have... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy Range("A7:A5000").Select ActiveSheet.Paste What happens is that when I print the file, it is trying to print rows 1 - 5000!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run formula only on cells with data
I like to just plop the formula into the range--instead of putting it once and
filling down--or copy|pasting. dim LastRow as long dim myFormula as string with activesheet myformula = "=IF(RC[9]&"", ""&RC[10]=""," _ & """,IF(RC[1]="""","""",RC[1]),RC[9]&"",""&RC[10])" 'I used column B to get the last used row lastrow = .cells(.rows.count,"B").end(xlup).row .range("a7:a" & lastrow) = myformula end with Rob wrote: I put in the range A7:A5000 so that I made sure the formula ran for all of my cells, but it is putting in a blank value in all cells below the used rows. Each file I run this macro on will include a different number of rows. So, I need to paste the forumla only on rows that have data (are not empty). For instance, select rows with data, and then paste formula. Right now I have... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy Range("A7:A5000").Select ActiveSheet.Paste What happens is that when I print the file, it is trying to print rows 1 - 5000!!! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to run formula only on cells with data
Worked perfectly! Thank you!
"Dave Peterson" wrote: I like to just plop the formula into the range--instead of putting it once and filling down--or copy|pasting. dim LastRow as long dim myFormula as string with activesheet myformula = "=IF(RC[9]&"", ""&RC[10]=""," _ & """,IF(RC[1]="""","""",RC[1]),RC[9]&"",""&RC[10])" 'I used column B to get the last used row lastrow = .cells(.rows.count,"B").end(xlup).row .range("a7:a" & lastrow) = myformula end with Rob wrote: I put in the range A7:A5000 so that I made sure the formula ran for all of my cells, but it is putting in a blank value in all cells below the used rows. Each file I run this macro on will include a different number of rows. So, I need to paste the forumla only on rows that have data (are not empty). For instance, select rows with data, and then paste formula. Right now I have... Range("A7").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"", ""&RC[10])" Range("A7").Select Selection.Copy Range("A7:A5000").Select ActiveSheet.Paste What happens is that when I print the file, it is trying to print rows 1 - 5000!!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula in macro or in sheet/cells? | Excel Worksheet Functions | |||
How to use macro to edit data in Excel cells | Excel Discussion (Misc queries) | |||
Formula/macro that ignores cells nonblanks | Excel Discussion (Misc queries) | |||
Macro to delete data in 'green' cells only | Excel Worksheet Functions | |||
repeat macro formula to all cells | Excel Discussion (Misc queries) |