macro recorder and formulas

D

Dave F

I just recorded a macro for which one of the steps is a rather complicated
ISNUMBER/MATCH function:

ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(MATCH(LEFT(RC[-18],3),'[ELR expense account
identification.xls]Sheet1'!R2C1:R12C1,0)),ISNUMBER(MATCH(RC[-17],'[Frank''s
expense codes--GDCS and
non-GDCS.xls]Sheet1'!R2C1:R39C1,0))),""Extract"","""")"

Two questions:
1) How do I get the macro to always insert this formula into cell T2?
2) Why does the macro recorder record in R1C1 format? Is it possible to get
it to record in regular A1 format?

Thanks,

Dave
 
P

Pete_UK

Dave,

1. This is going to put the formula in the activecell, so to make
sure the activecell is T2 have a line before it:

Range("T2").Select

2. The macro recorder always seems to use R1C1 format - I don't know
if there is a setting to change this, but you can always edit the
formula after recording the macro.

Hope this helps.

Pete
 
D

Dave F

Thanks, just figured 1 out, and as for number 2 I suppose it doesn't matter
as long as I know the formula works in A1 format.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


Pete_UK said:
Dave,

1. This is going to put the formula in the activecell, so to make
sure the activecell is T2 have a line before it:

Range("T2").Select

2. The macro recorder always seems to use R1C1 format - I don't know
if there is a setting to change this, but you can always edit the
formula after recording the macro.

Hope this helps.

Pete


I just recorded a macro for which one of the steps is a rather complicated
ISNUMBER/MATCH function:

ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(MATCH(LEFT(RC[-18],3),'[ELR expense account
identification.xls]Sheet1'!R2C1:R12C1,0)),ISNUMBER(MATCH(RC[-17],'[Frank''s
expense codes--GDCS and
non-GDCS.xls]Sheet1'!R2C1:R39C1,0))),""Extract"","""")"

Two questions:
1) How do I get the macro to always insert this formula into cell T2?
2) Why does the macro recorder record in R1C1 format? Is it possible to get
it to record in regular A1 format?

Thanks,

Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top