Macro Recorder Question

[Nalani]

When using the Recorder to help me, I get the following:

 Range("K15").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-9]=RC[2],1,0)"
    Range("K16").Select

I do understand what the R1C1 is saying but -

I don't particually like the R1C1 annotation and would prefer to reference the actual cells when writing my code.

How can I (if possible) set the recorder so it will give me:

=IF(B18=M15,1,0)

 

[Nalani]

bump

 

[chuckchuckit]

Hi Nalani,

From post #8, would be interested in seeing the code generated.

Chuck

 

[Nalani]

1. Turn on recorder
2. Select cell K15
3. Manually type =IF(
4. Click on B18
5. Type =
6. Click on M15
7. Type ,1,0)
8. Hit Enter key

Code generated:

Sub Macro3()
'
' Macro3 Macro
'
'
    Range("K15").Select
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-9]=RC[2],1,0)"
    Range("K16").Select
End Sub

 

[Nalani]

And if I just type in the whole formula without clicking on cells:

Sub Macro8()
'
' Macro8 Macro
'
'
    ActiveCell.FormulaR1C1 = "=IF(R[3]C[-9]=RC[2],1,0)"
    Range("K16").Select
End Sub

Producing the same thing.

I'm looking for the Recorder to produce something in the line of:

ActiveCell.Formula = "IF(B18=M15,1,0,)"

or

 "IF(Range("B18")=Range("M15"),1,0)"

 

[Biz]

Try code below

Sub Test()
Range("K15").Formula = "=IF(B18=M15,1,0)"
End Sub

Does it work for you?

Biz

 

[Nalani]

Thanks Biz

Yes that does put the formula in cell K15, but that was not my initial question.

The question is in regards to having the Recorder record the formula as you posted or similar to what I posted in my post #14 above.

 

[Biz]

To be honest I am beginner who uses macro recorder to generate the code then I streamline it.

I am sure someone may be able to help you with your question.

Biz <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Nalani]

To be honest I am beginner who uses macro recorder to generate the code then I streamline it.

I am sure someone may be able to help you with your question.

Biz <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Thanks Biz, I'm a begginner too and just trying to find out if the Recorder can be set to produce a non R1C1 formula.

Using the IF formula was just an example. It occours with all formulas as far as I see.

SUM Formula from Recorder:

ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    Range("K25").Select

 

[dk]

I'm fairly certain that there's no way to get it to record in the A1 style and there are other threads that say the same e.g.

http://www.mrexcel.com/forum/showthread.php?t=37382

Once you get used to it the R1C1 notation is not actually that bad and in many cases it's easier to use within code - it's much easier to add 20 columns to column 10 than it is to add 20 columns to column J for example.

Regards
DK

 

[chuckchuckit]

Nalani,

You are certainly correct as that is what the recorder is doing. I did not know either that the recorder used R1C1 notation for formulas like that.

When I first started coding again about a year or so ago, it was a bit of a switch for me to start using the R1C1 formulas. But like dk said, it is a lot easier to use for many things. Most people don't know how many columns over col 'L' is. But col 12 is obvious. Column HW gets really tough for me to figure...

Also the using "Cells(2, 5)" instead of same location using "Range("E2")" gets a lot easier to modify rows and cols with the "Cells" approach. As "Cells" also uses Row and Col, meaning Cells(2, 5) the 2 is Row, the 5 is Col.

Then of course any Cells R or C can be a variable too, or numerous variables for each one. I have some R's or C's that might have 4 or 5 variables for just the 'R' or the 'C'. It gets really adaptable etc for many uses.

But then you may have already known much of this perhaps.

Chuck