software

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.
Andy,

You don't really need to quot;pastequot; 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(quot;C6quot;).Select
ActiveCell.FormulaR1C1 = quot;'=C5*3 C4quot;

You can then edit that down to one line:

Range(quot;C6quot;).Formula = quot;=C5*3 C4quot;

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(quot;C6quot;).Select
ActiveCell.FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;

You can then edit that down to one line:

Range(quot;C6quot;).FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;

Which you can expand to your multiple cells:

Range(quot;C6:C100quot;).FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;HTH,
Bernie
MS Excel MVPlt;Andygt; wrote in message ...
gt; Hi all
gt;
gt; Thanks for reading this. I'm new to macros, so be gentle with me!
gt; I have recorded a macro to import a file and run a couple of jobs on it. When I look at the macro,
gt; it is written with relative references (like [RC]-2 and stuff). Is there any way of changing it so
gt; that I can understand it (like H3)?
gt; Also , I have written a complex formula which I want the macro to paste into a cell - to save
gt; having to retype it. The problem is that when I record the macro, the pasting of the formula into
gt; the cell becomes 'ActiveSheet.paste' rather than pasting the actual formula into the cell.
gt;
gt; Cheers.
gt;
Thanks very much! That's sorted it. Much appreciated.

I did apologise for posting the question twice, by the way! Sorry again.

Cheers.
Andy.

quot;Bernie Deitrickquot; lt;deitbe @ consumer dot orggt; wrote in message
...
gt; Andy,
gt;
gt; You don't really need to quot;pastequot; per se, if you know the formula.
gt;
gt; The best way to deal with formulas is to have the formula already in the
gt; cell where you want it, written and working. Once you do, start your macro
gt; recorder. Select the cell, press F2, hit home, type a single quote, and
gt; press enter. For example, you'll get something like this:
gt;
gt; Range(quot;C6quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;'=C5*3 C4quot;
gt;
gt; You can then edit that down to one line:
gt;
gt; Range(quot;C6quot;).Formula = quot;=C5*3 C4quot;
gt;
gt; by taking out the single quote and changing FormulaR1C1 to Formula.
gt;
gt; But if you want that formula in multiple cells, you'll want to leave it as
gt; FormulaR1C1. Record again, reselect the cell, press home, press delete
gt; once to get rid of the single quote, and then press enter. (You con't
gt; need to start with a commented out formula - pressing F2 and Enter will
gt; get you here, if your formula is working...) Your recorded code will look
gt; like:
gt;
gt; Range(quot;C6quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;
gt;
gt; You can then edit that down to one line:
gt;
gt; Range(quot;C6quot;).FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;
gt;
gt; Which you can expand to your multiple cells:
gt;
gt; Range(quot;C6:C100quot;).FormulaR1C1 = quot;=R[-1]C*3 R[-2]Cquot;
gt;
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; lt;Andygt; wrote in message ...
gt;gt; Hi all
gt;gt;
gt;gt; Thanks for reading this. I'm new to macros, so be gentle with me!
gt;gt; I have recorded a macro to import a file and run a couple of jobs on it.
gt;gt; When I look at the macro, it is written with relative references (like
gt;gt; [RC]-2 and stuff). Is there any way of changing it so that I can
gt;gt; understand it (like H3)?
gt;gt; Also , I have written a complex formula which I want the macro to paste
gt;gt; into a cell - to save having to retype it. The problem is that when I
gt;gt; record the macro, the pasting of the formula into the cell becomes
gt;gt; 'ActiveSheet.paste' rather than pasting the actual formula into the cell.
gt;gt;
gt;gt; Cheers.
gt;gt;
gt;
gt;
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

gt; Thanks very much! That's sorted it. Much appreciated.
gt;
gt; I did apologise for posting the question twice, by the way! Sorry again.