If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relative cells in macro - and pasting a formula too!
Hi all
Thanks for reading this. I'm new to macros, so be gentle with me! I have recorded a macro to import a file and run a couple of jobs on it. When I look at the macro, it is written with relative references (like [RC]-2 and stuff). Is there any way of changing it so that I can understand it (like H3)? Also , I have written a complex formula which I want the macro to paste into a cell - to save having to retype it. The problem is that when I record the macro, the pasting of the formula into the cell becomes 'ActiveSheet.paste' rather than pasting the actual formula into the cell. Cheers. |
#2
|
|||
|
|||
Relative cells in macro - and pasting a formula too!
Andy,
You don't really need to "paste" per se, if you know the formula. The best way to deal with formulas is to have the formula already in the cell where you want it, written and working. Once you do, start your macro recorder. Select the cell, press F2, hit home, type a single quote, and press enter. For example, you'll get something like this: Range("C6").Select ActiveCell.FormulaR1C1 = "'=C5*3+C4" You can then edit that down to one line: Range("C6").Formula = "=C5*3+C4" by taking out the single quote and changing FormulaR1C1 to Formula. But if you want that formula in multiple cells, you'll want to leave it as FormulaR1C1. Record again, reselect the cell, press home, press delete once to get rid of the single quote, and then press enter. (You con't need to start with a commented out formula - pressing F2 and Enter will get you here, if your formula is working...) Your recorded code will look like: Range("C6").Select ActiveCell.FormulaR1C1 = "=R[-1]C*3+R[-2]C" You can then edit that down to one line: Range("C6").FormulaR1C1 = "=R[-1]C*3+R[-2]C" Which you can expand to your multiple cells: Range("C6:C100").FormulaR1C1 = "=R[-1]C*3+R[-2]C" HTH, Bernie MS Excel MVP Andy wrote in message ... Hi all Thanks for reading this. I'm new to macros, so be gentle with me! I have recorded a macro to import a file and run a couple of jobs on it. When I look at the macro, it is written with relative references (like [RC]-2 and stuff). Is there any way of changing it so that I can understand it (like H3)? Also , I have written a complex formula which I want the macro to paste into a cell - to save having to retype it. The problem is that when I record the macro, the pasting of the formula into the cell becomes 'ActiveSheet.paste' rather than pasting the actual formula into the cell. Cheers. |
#3
|
|||
|
|||
Relative cells in macro - and pasting a formula too!
Thanks very much! That's sorted it. Much appreciated.
I did apologise for posting the question twice, by the way! Sorry again. Cheers. Andy. "Bernie Deitrick" deitbe @ consumer dot org wrote in message ... Andy, You don't really need to "paste" per se, if you know the formula. The best way to deal with formulas is to have the formula already in the cell where you want it, written and working. Once you do, start your macro recorder. Select the cell, press F2, hit home, type a single quote, and press enter. For example, you'll get something like this: Range("C6").Select ActiveCell.FormulaR1C1 = "'=C5*3+C4" You can then edit that down to one line: Range("C6").Formula = "=C5*3+C4" by taking out the single quote and changing FormulaR1C1 to Formula. But if you want that formula in multiple cells, you'll want to leave it as FormulaR1C1. Record again, reselect the cell, press home, press delete once to get rid of the single quote, and then press enter. (You con't need to start with a commented out formula - pressing F2 and Enter will get you here, if your formula is working...) Your recorded code will look like: Range("C6").Select ActiveCell.FormulaR1C1 = "=R[-1]C*3+R[-2]C" You can then edit that down to one line: Range("C6").FormulaR1C1 = "=R[-1]C*3+R[-2]C" Which you can expand to your multiple cells: Range("C6:C100").FormulaR1C1 = "=R[-1]C*3+R[-2]C" HTH, Bernie MS Excel MVP Andy wrote in message ... Hi all Thanks for reading this. I'm new to macros, so be gentle with me! I have recorded a macro to import a file and run a couple of jobs on it. When I look at the macro, it is written with relative references (like [RC]-2 and stuff). Is there any way of changing it so that I can understand it (like H3)? Also , I have written a complex formula which I want the macro to paste into a cell - to save having to retype it. The problem is that when I record the macro, the pasting of the formula into the cell becomes 'ActiveSheet.paste' rather than pasting the actual formula into the cell. Cheers. |
#4
|
|||
|
|||
Relative cells in macro - and pasting a formula too!
Andy,
You're welcome. Cross-posting can only result in unnecessary effort - most of these groups are monitored by the same people anyway, which is why I saw your cross-post. But if I hadn't seen it and didn't reply to it, someone might have done so later, when seeing an un-answered post, not knowing that I had answered your question here. Bernie MS Excel MVP Thanks very much! That's sorted it. Much appreciated. I did apologise for posting the question twice, by the way! Sorry again. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
2 Nesting questions | Starchaser | Worksheet Functions | 7 | January 20th, 2006 06:53 PM |
Perform oiperations relative to initial selected cell | scratching my head | General Discussion | 1 | May 30th, 2005 05:42 PM |
Excel 1004 Error When Pasting Special W/ Macro | setoFairfax | General Discussion | 2 | April 27th, 2005 02:28 PM |
Playing a macro from another workbook | Jim | General Discussion | 1 | February 23rd, 2005 10:12 PM |
copying excel cells with link via macro | ikeriba | Powerpoint | 1 | July 9th, 2004 03:16 PM |